Using a variable as an argument

[jersiq]

I have a spreadsheet where I am auto-filtering the data, creating new sheets
based on the filters, then graphing the results.
The row length of my data can be variable, and in my macro, I want to be
able to paste the variable length of the data to one of the new sheets.

For example, on the "raw data" page, I filter two criteria and copy the
column with data I need to another worksheet.
Earlier in the macro I do define the row length, using an "IF" statement
combined with a "SUM" statement, and assign it to the variable row_count.
Now later in the macro, I would like to call that variable into my paste
function.

Selection.AutoFilter Field:=4, Criteria1:="1"
Selection.AutoFilter Field:=3, Criteria1:="1"
Range("G2:G(I WANT MY VARIABLE HERE)").Select

The above of course, gives me errors.

Now later in the macro, which I haven't written yet, I will also need to
call this variable, as it will determine the source data for a chart plotted
over time.

Is the way I am attempting this going to cause problems with the chart in
the fuiture?

 

[Dave Peterson]

Range("G2:G" & yourvariablehere).Select

I have a spreadsheet where I am auto-filtering the data, creating new sheets
based on the filters, then graphing the results.
The row length of my data can be variable, and in my macro, I want to be
able to paste the variable length of the data to one of the new sheets.

For example, on the "raw data" page, I filter two criteria and copy the
column with data I need to another worksheet.
Earlier in the macro I do define the row length, using an "IF" statement
combined with a "SUM" statement, and assign it to the variable row_count.
Now later in the macro, I would like to call that variable into my paste
function.

Selection.AutoFilter Field:=4, Criteria1:="1"
Selection.AutoFilter Field:=3, Criteria1:="1"
Range("G2:G(I WANT MY VARIABLE HERE)").Select

The above of course, gives me errors.

Now later in the macro, which I haven't written yet, I will also need to
call this variable, as it will determine the source data for a chart plotted
over time.

Is the way I am attempting this going to cause problems with the chart in
the fuiture?

 

[JLGWhiz]

In your narrative you talk about row length, but in your illustration you
indicate column length as the variable you want. So here are both:

Variable Column G length: LastRow = Cells(Rows.Count, 7).End(xlUp).Row

Variable Row 2 length: LastCol = Cells(2, Columns.Count).End(xlToLeft).Column

Inserted in your code:

Range("G2:G" & LastRow).Select 'to select the column

Range("G2", Cells(2, LastCol)).Select 'to select the row

 

[DomThePom]

Not totally clear what you are trying to do here....

Howeever, if you are filitering and then copying filtered data to another
sheet that will for the basis for a chart......

You should never need to refer directly to an address as you are suggesting
here. Normally in Excel everything should be done using ranges, specialcells
and end

For example if you want to do the filter as you suggested then copy the
first 2 columns of the filtered data to another sheet then the code would be
as follows (assuming your table to be filtered and copied start on sheet1!A1:

sub CopyPasteFilteredData

dim rng as range
dim rngChartData as range
set rng = sheets("Sheet1").cells(1,1).currentregion
with rng
.autofilter Field:=4, Criteria1:="1"
.autofilter Field:=3, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).resize(,2).copy
end with
sheets("Sheet2").cells(1,1).paste
set rngChartData = sheets("Sheet2").cells(1,1).currentregion

end sub