Trying to set VBA range to last row with values--duplicate declaration error

[HeatherF]

Hello,
I'm creating an excel template with a formatting macro. The template will have data of varying length added to it (could be 10 rows, could be 1000). I created a macro using Record Macro, then added a section to add a border after each unique value in the last column. It's working great (see VBA code below), except that the sort columns section of the code is specifying row 357 as the end of the range (the last row of my test data). When I tried using the macro on 400 rows, the sorting restarted after 357.

I tried replacing this:
Key:=Range("T2:T357" _
),

With this:
Key:=Range("T2:T" & LastRow)

Or this:
Key:=Range("T:T")

But when I do that I get an error message that highlights "LastRow as Long" in the next command and gives me this error: Compile error: Duplicate declaration in current scope

Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("T2:T357" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B2:B357" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AV357")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim LastRow As Long
    Dim xrg As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each xrg In Range("AV2:AV" & LastRow)
        If xrg <> xrg.Offset(1, 0) Then
            Range("A" & xrg.Row & ":AV" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlContinuous
        End If
    Next xrg
    
End Sub

Thank you for your help!!!

 

[Fluff]

That error means you have already declared LastRow & you can only do it once in a procedure. Can you post all your code that is giving the error.

 

[HeatherF]

That error means you have already declared LastRow & you can only do it once in a procedure. Can you post all your code that is giving the error.

This is the entire macro, with the edits I tried to make to specify LastRow. It's a little long, but the last two commands are the ones affected. Thank you!

Sub cleanup()
'
' cleanup Macro
' copy ID# column and formatting
'

'
    Columns("B:B").Select
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=1
    Columns("AV:AV").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("J:J").Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.LargeScroll ToRight:=1
    Columns("AG:AG").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("AO:AP").Select
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    ActiveWindow.LargeScroll ToRight:=-1
    Columns("S:T").Select
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("T2:T" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AV357")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim LastRow As Long
    Dim xrg As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each xrg In Range("AV2:AV" & LastRow)
        If xrg <> xrg.Offset(1, 0) Then
            Range("A" & xrg.Row & ":AV" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlContinuous
        End If
    Next xrg
    
End Sub

 

[Fluff]

You have only declared the variable once, so you would not get

Compile error: Duplicate declaration in current scope

However you are trying to use it, before you have given it a value.

Try it like

    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("T2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:AV" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Dim xrg As Range
    For Each xrg In Range("AV2:AV" & LastRow)
        If xrg <> xrg.Offset(1, 0) Then
            Range("A" & xrg.Row & ":AV" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlContinuous
        End If
    Next xrg

 

[HeatherF]

Ah! I understand, I was using LastRow above the line that specified what that meant. It's working now, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.