Find Last Row with data and enabled Sorting Macro

**emina002** · 06-17-2011, 05:29 PM

Hi to all.Hoping you have a good day.I would like seek help or advices regarding my program. Please see attachment to visualize my problem.

First I have 4 column A to D. that reviews all the tabs name in a workbook,it was reviewed based on their position. I would like to sort out the value of column A after the word "Template". I can record a macro and since my program is to review all tabs in a workbook my last tab was "Template" and If I unhide one tab "template" will be include in sorting.How can i determine the row "template" and after that I perform the sorting?

Here is my recorded macro. In my sample file the Worksheet is "Sheet1",my original worksheet named Index.Thanks for the big help.

 ActiveWorkbook.Worksheets("Index").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Index").Sort.SortFields.Add Key:=Range("A16"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Index").Sort
        .SetRange Range("A16:A100")
        .Header = xlNo
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
       .Apply
     
      End With

**mojo249** · 06-17-2011, 06:04 PM

Hi

See your updated workbook attached.

Dion

Sub SortData()

Dim ws As Worksheet
Dim varRow As Long
Dim varFirstRow As Long
Dim varLastRow As Long
Dim rng As Range

Set ws = ThisWorkbook.Sheets("Sheet1")
varLastRow = ws.Range("A60000").End(xlUp).Row

'Find varFirstRow
For varRow = 2 To varLastRow
    If ws.Cells(varRow, 1).Value = "Template" Then
        varFirstRow = varRow + 1
    End If
Next varRow

With ws.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(Cells(varFirstRow, 1), Cells(varLastRow, 1)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    .SetRange Range(Cells(varFirstRow, 1), Cells(varLastRow, 4))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

**emina002** · 06-17-2011, 06:08 PM

@mojo249 Thanks that was a big help

**protonLeah** · 06-17-2011, 06:20 PM

maybe:

    Dim TemplateRow As Variant, _
        LastARow    As Long
        
    LastARow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Set TemplateRow = Sheets("sheet1").Range("A:A").Find("Template")
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(TemplateRow.Row, 1), Cells(LastARow, 1))
        .Header = xlNo
        .MatchCase = True
       .Apply
    End With