Creating a variable row for sorting in a macro

[triplel1977]

I am a newbie in macros, so I am basically using the recorder to create them.

I am dipping my fingers into editing, so my current problem is, how to edit the code so, regardless of the number of rows in a column it will sort all of them.

If I am reading the code correctly, he sample below says the range is going to sort to row N30, but next week I may have 150 rows. How do I write it for relative versus absolute (did I get that verbiage right?)

Thanks in advance.

Sample

ActiveWorkbook.Worksheets("BICSI_Billing").Sort.SortFields.Add Key:=Range( _
"N2:N30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("BICSI_Billing").Sort
.SetRange Range("A1:W30")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[VoG]

Try

Dim LR As Long
LR = Worksheets("BICSI_Billing").Range("N" & Rows.Count).End(xlUp).Row
ActiveWorkbook.Worksheets("BICSI_Billing").Sort.SortFields.Add Key:=Range( _
"N2:N" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("BICSI_Billing").Sort
    .SetRange Range("A1:W" & LR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

 

[triplel1977]

Thanks

I downloaded a test file with more rows

It appears to work, after it hit an error and I deleted the code for removing duplicate Invoice #s in column 10 and then I reran it. So I think I'm good.

Code Removed:

ActiveSheet.Range ("$A$1:$W$30").RemoveDuplicates Columns:=10, Header:=x1Yes

It goes back to the same type of problem on a different column. I need to get educated on this. Any recommendations?

 

[VoG]

Take a look at Hiker's List http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[triplel1977]

Great resource, thanks. I've added to my favorites.