VBA Excel detect always hidden rows and columns in With x End With loop?

Viewed 841

How to detect always in With xx end Withalso hidden columns and rows? My code's current variables values are changing depending on that are the columns and rows hidden or not. That prevents my code working on properly.

With myMatrixSheet
            lastRow = .Range("B" & .Rows.Count).End(xlUp).row
            lastColumn = .Cells(7,.UsedRange.Columns.Count).End(xlToLeft).column + 1
End With

My solved and final code look likes below

With myMatrixSheet

If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    lastRow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).row
Else
    lastRow = 1
End If

If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    lastColumn = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByColumns, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).column
Else
    lastColumn = 1
End If
End With
2 Answers
Related