Hi all,

I recorded a macro that sorts a spreadsheet by 4 columns. Below is the VBA that was recorded. How do I replace the fixed range (for example: Range("B2:B867") ) with the row count instead of 867? Thank you!

Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("F2:F867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("L2:L867") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:N867")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Hi

You can try something like this

SpecialCells is one way to identify the last used cell in a sheet.

There are otherways like UsedRange, xlEnd etc

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Thank you, Shasur. The SpecialCells solution worked!