VBA code for last row instead of naming a range

[Patriot2879]

Hi please can you help me, i have the code below where i have named a range A28:C41 but the last row might not always be C41 please can you advise how i do a last row please?

With ActiveSheet.Sort
     .SortFields.Add Key:=Range("A28"), Order:=xlAscending
     .SortFields.Add Key:=Range("B28"), Order:=xlAscending
     .SetRange Range("A28:C41")
     .Header = xlYes
     .Apply
End With

 

[jmacleary]

Hi

Try this:

lastrow = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row
With ActiveSheet.Sort      
     .SortFields.Add Key:=Range("A28"), Order:=xlAscending
     .SortFields.Add Key:=Range("B28"), Order:=xlAscending
     .SetRange Range("A28:C"&lastrow)
     .Header = xlYes
     .Apply
End With

 

[Patriot2879]

hi thank you for the help, i have tried this but i i get an error on the (.Rows bit on the 1st line, hope you can advise

 

[jmacleary]

Sorry - I copied it from one of my workbooks and didn't amend it fully. Try this instead:

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

 

[Patriot2879]

brilliant thank you for the help again, i dont know what i would do without all your help, i am still learning this but enjoyijng it as well