Thread: Sheet event code: find last row broken?

I'm making a macro to show or hide rows depending on whether or not a checkbox is checked. I'm using Wingdings font checkboxes, not actual form controls.

The question: My macro can't find the last row of the used range on the sheet. This is in the sheet's code, set to fire when the selection changes.

Normally the line in blue works just fine, but it's not working here. In the attached workbook, the macro thinks the last used row is row 19, when it's actually row 21. What's going on?

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim LastRow As Integer
Dim i As Integer
Dim j As Integer
Dim testcol As Integer
Dim rng As Range
Dim blankrng As Range
Dim lastblank As Range

LastRow = ActiveSheet.UsedRange.Rows.Count
Set rng = Intersect(Target, Range("A1:C" & LastRow))

If Not rng Is Nothing Then 'if clicked cell is in A1:Clastrow and contains £ or R
If rng.Cells.Count = 1 Then
If (rng.Value = "£" Or rng.Value = "R") Then
    
    LastRow = ActiveSheet.UsedRange.Rows.Count
    testcol = rng.Column
    
    For i = (rng.Row + 1) To LastRow
        For j = 1 To testcol
            If (Cells(i, j).Value = "£" Or Cells(i, j).Value = "R") Then
                Set lastblank = Cells(i, testcol).Offset(-1, 0)
                GoTo FoundLBlank
            End If
        Next j
        If Cells(i, testcol).Row = LastRow Then
            Set lastblank = Cells(i, testcol)
            GoTo FoundLBlank
        End If
    Next i
    
FoundLBlank:
    
    Set blankrng = Range(rng.Offset(1, 0), lastblank)
    
    If rng.Cells.Count = 1 Then
        rng.Font.Name = "Wingdings 2"
            If rng.Value = "R" Then
            rng.Value = "£"
            blankrng.EntireRow.Hidden = True
        Else
            rng.Value = "R"
            blankrng.EntireRow.Hidden = False
        End If
    End If
End If
End If
End If

End Sub

Code:

With ActiveSheet.UsedRange
    LastRow = .Row + .Rows.Count - 1
End With

To see why you need to do this, in the Immediate window, enter

Code:

? ActiveSheet.usedrange.address

Whoa, thank you, very awesome! Worked like a charm, after I replaced the first instance of that line and deleted the second.