CODEWITHSUNDEEP
excelvba
Sabatino Ognibene
Sabatino Ognibene

Reputation: 171

VBA to Find Last Row with unknown column, and Last Column with unknown row

To find the last row or column, I'll typically I'll use something like:

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column

However, I'm trying to find the furthest right column with data in a case where there are no column headers, and the column I'm hoping to find could be column B (IE "B27") or it could be column Z (IE "Z1000"), or any random cell.

Similarly, I'm also trying to find the last row on the same sheet, with no column headers, where the last row could be in any column.

Knowing how to do that could help in several scenario's, but to elaborate on my current scenario, I'm trying to concatenate some lines that were copied from other sources and had many tabs. Sometimes tabs are at the beginning of the line and sometimes they are in the middle. This of course pushes each line into multiple cells, sometimes very far to the right.

Usually these documents are not more than 10k lines, so I thought about writing a "brute-force" type of code like:

Private Sub Concatenate_20k_Rows()

    Dim ws As Worksheet
    Dim cell As Range
    Dim LC As Long, i As Long
    
    Set ws = ActiveSheet
    
    For Each cell In ws.Range("A1:A20000")
        LC = Cells(cell.Row, Columns.Count).End(xlToLeft).Column
        For i = 1 To LC
            cell.Value = cell.Value & " " & cell.Offset(0, i).Value
        Next i
        cell.Value = Application.Trim(cell.Value)
    Next cell

End Sub

I suppose finding the last column with unknown headers won't be as important for my scenario, but finding the last row could be useful so I don't have to brute-force through 20k rows if there's only 2k rows, or don't have to risk not going far enough in the off chance I have more than 20k rows.

Upvotes: 0

Views: 1408

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can use the Range.Find method.
As written, this will return a 2 element array where

  • the first element = Row number
  • and the second element = column number.
Option Explicit
Function LastRC(Worksht As String) As Long()
'Uncomment if on worksheet
'Application.Volatile
    Dim WS As Worksheet, R As Range
    Dim LastRow As Long, LastCol As Long
    Dim L(1) As Long
    Dim searchRng As Range

Set WS = Worksheets(Worksht)
Set searchRng = WS.Cells

With searchRng
    Set R = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                    searchdirection:=xlPrevious)

    If Not R Is Nothing Then
        LastRow = R.Row
        LastCol = .Cells.Find(what:="*", after:=.Cells(1, 1), _
                    LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
                    searchdirection:=xlPrevious).Column
    Else
        LastRow = 1
        LastCol = 1
    End If
End With

L(0) = LastRow
L(1) = LastCol
LastRC = L
End Function

Upvotes: 1

Related Questions