Macro question - Selecting varying ranges of cells.

[Guest]

Hi,

I have a macro set up to automate some tasks I am responsible for. Part of
the macro is to go to sheet2 and select the entire range of data. When
recording the macro I went to the start of the data (cell A2) and pressed
Ctrl + Shift + down arrow.

The resultant range came out to be A2:F307. I ran the macro on another set
of data and it selected the same exact range of cells (A2:F307) when it needs
to be A2:F272.

Is there a way to change the macro so it knows to dynamically select the
range of data each time it's run?

 

[Don Guillett]

You should always post YOUR code for comments. Assuming Col A is the longest
column.

lr=cells(rows.count,"a").end(xlup).row
range("a2:f" & lr).copy

 

[KC Rippstein]

Dim LastRow As Long

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

 

[Gord Dibben]

Jim

Select A2:F2 then run this.

Sub select_alldown()
Set rng1 = Selection
Range(rng1, Cells(Rows.Count, rng1.Column).End(xlUp)).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End Sub


Gord Dibben MS Excel MVP