how to define variable row count in macros?

[ansvk1]

Hi there,
I would to ask for a little help with learning how to change my row values to a variable while i was trying to customise/write this VBA code (from recorded macro) to suit my needs.

here is the

Sub Macro2()


    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Clear
    'now sorting for Levles from 4 to 1
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Add Key:= _
        Range("N2:N189" & xlDown??), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    'now sorting for Responsible Parties
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Add Key:= _
        Range("T2:T189" & xlDown??), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

i am trying to change the selection part form N2:N189 to N2:NxlDown (so that it selcts however many row are present later on). How can i do that?
Thanks in advance!

 

[Tetra201]

Try using

Range("N2:N" & Cells(Rows.Count, "N").End(xlUp).Row)

 

[Fluff]

Try

Sub Macro2()

    [COLOR=#0000ff]Dim UsdRws As Long

    UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1").Select[/COLOR]
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Clear
    'now sorting for Levles from 4 to 1
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Add Key:= _
        Range("[COLOR=#0000ff]N2:N" & UsdRws[/COLOR]), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    'now sorting for Responsible Parties
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Add Key:= _
        Range("[COLOR=#0000ff]T2:T" & UsdRws[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

 

[ansvk1]

Try using

Range("N2:N" & Cells(Rows.Count, "N").End(xlUp).Row)

this worked, well! Thanks for the suggestion

 

[ansvk1]

Try

Sub Macro2()

    [COLOR=#0000ff]Dim UsdRws As Long

    UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     [/COLOR]
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Add Key:= _
        Range("[COLOR=#0000ff]N2:N" & UsdRws[/COLOR]), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    'now sorting for Responsible Parties
    ActiveWorkbook.Worksheets("Summary").AutoFilter.Sort.SortFields.Add Key:= _
        Range("[COLOR=#0000ff]T2:T" & UsdRws[/COLOR]), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal

Worked just the same as the other suggestion, another interesting way to do it... thanks for the suggestion Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[ansvk1]

A quick question on both suggestions.

Do i have to have my code lines below to make this macro run correctly?

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

in order for the excel to do the custome sorting correctly (that i used) to record this macro. Or, it doesn't matter and i can just discar the above 2 lines of code as well? Please let me know.

thanks!

 

[Tetra201]

@ ansvk1:

You are welcome.

There are many ways to get the last used row.

The one that I suggested finds the last used row in a specific column.

Fluff's suggestion finds the last used row in the entire worksheet.

 

[Fluff]

I suspect that you can get rid of them, but the easiest way to find out, is try it.

 

[Tetra201]

I second Fluff's comment.

You do not use the selection in your subsequent code. So, those two lines can be removed.