CODEWITHSUNDEEP
excelvba
Antti
Antti

Reputation: 275

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

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

Upvotes: 3

Views: 1224

Answers (2)

Vityata
Vityata

Reputation: 43593

To get the last of the rows/columns also when they are hidden:

Activesheet.cells.SpecialCells(xlCellTypeLastCell).Row Activesheet.cells.SpecialCells(xlCellTypeLastCell).Column

If you need the specific column, use it like this:

Public Function GetLastRow(lngCol As Long) As Long   

    GetLastRow = ActiveSheet.Columns(3).Find(What:="*", SearchDirection:=xlPrevious).Row  

End Function

In general, this is probably the best article for last rows and columns: https://www.rondebruin.nl/win/s9/win005.htm

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

I find FIND the easiest way (other methods are available): 

Sub Test()

    Dim LastRow As Long
    Dim LastColumn As Long

    With Sheet1
        LastRow = .Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
        LastColumn = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
    End With

    If LastRow = 0 Then LastRow = 1
    If LastColumn = 0 Then LastColumn = 1

    '--OR USE SEPARATE FUNCTION--

    Dim rLastCell As Range
    Set rLastCell = LastCell(Sheet1)
    Debug.Print rLastCell.Row & " : " & rLastCell.Column

End Sub

Public Function LastCell(wrkSht As Worksheet) As Range

    Dim lLastCol As Long, lLastRow As Long

    On Error Resume Next

    With wrkSht
            lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row

        If lLastCol = 0 Then lLastCol = 1
        If lLastRow = 0 Then lLastRow = 1

        Set LastCell = .Cells(lLastRow, lLastCol)
    End With
    On Error GoTo 0

End Function

The FIND solution will be covered in the link provided by @Vityata.

Upvotes: 2

Related Questions