Sort and Autofill Range

[marka87uk]

Hi,

I've recorded a macro to sort some data I have to do on a regular basis. The problem is the number of rows can change but the macro recorded references to the range I've recorded.

e.g.
Range("A1:S2429").Sort
Selection.AutoFill Destination:=Range("C2:C1249")

I can I make it sort all of the data, and likewise, autofill to the bottom row?

Thanks!

 

[VoG]

Try like this

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:S" & LR).Sort
Selection.AutoFill Destination:=Range("C2:C" & LR)

 

[Domski]

You can detect the last row in a column and hold it in a variable like this:

Dim lngLastRow As Long
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:S" & lngLastRow).Sort
Selection.AutoFill Destination:=Range("C2:C" & lngLastRow)

Dom

 

[marka87uk]

Cheers guys!