Loop through each sheet to find last row

[Av8tordude]

ok...I'm stump.

I'm trying to loop through each sheet with the following code.

Sheet 1, the last row is 213.
Sheet 2, the last row 7.

The code doesn't seem to recognize the last row for each sheet. How do I get the code to recognize the last row for each sheet.

For Each Ws In Sheets
        If Ws.Name <> "Sheet3" Then
            With Range("A7:R" & LRow)
                .SpecialCells(xlConstants).Font.Color = RGB(242, 242, 242)
                .Borders(xlEdgeLeft).Color = RGB(242, 242, 242)
                .Borders(xlEdgeRight).Color = RGB(242, 242, 242)
                .Borders(xlInsideVertical).Color = RGB(242, 242, 242)
                If Not IsLoaded("frmData") Then .Borders(xlEdgeBottom).Color = RGB(242, 242, 242)
            End With
            
            If ActiveCell.Row > 6 And Not IsEmpty(ActiveCell) Then
                With ActiveCell.Resize(, 18)
                    .Font.Color = 0
                    .Borders(xlEdgeLeft).Color = 0
                    .Borders(xlEdgeRight).Color = 0
                    .Borders(xlInsideVertical).Color = 0
                End With
            End If
            Rng.SpecialCells(xlConstants).Font.Color = RGB(14, 33, 56)
        End If
    Next

 

[Joe4]

A few things. Looping through your sheets doesn't actually select/activate it, unless you tell it to.
And if each sheet has a different last row, then LastRow must be calculated for each sheet.

Assuming you can use column A to find the last row, maybe this will give you what you need:

For Each ws In Sheets
        If ws.Name <> "Sheet3" Then
            With ws
                LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
                With .Range("A7:R" & LRow)
                    .SpecialCells(xlConstants).Font.Color = RGB(242, 242, 242)
                    .Borders(xlEdgeLeft).Color = RGB(242, 242, 242)
                    .Borders(xlEdgeRight).Color = RGB(242, 242, 242)
                    .Borders(xlInsideVertical).Color = RGB(242, 242, 242)
                    If Not IsLoaded("frmData") Then .Borders(xlEdgeBottom).Color = RGB(242, 242, 242)
                End With
            End With

             ...

As for the second block, I recommend avoid using "ActiveCell", especially if you are looping through different sheets, as it will only apply to the sheet you started on, unless you activate a different sheet. If that is something that you want to apply to the sheet you start on only, then move that above the other "With" code.

 

[Av8tordude]

Thank you Joe for the information and help

 

[Joe4]

You are welcome.