CODEWITHSUNDEEP
vbaexcel
hY8vVpf3tyR57Xib
hY8vVpf3tyR57Xib

Reputation: 3915

VBA find last ROW with values without checking each cell

for my excel VBA project I have the following code that finds my last row that contains any data. It checks each row in my sheet and looks if a value is inserted (bottom up):

lRow = Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

This works perfectly but the problem is that if loops over all records, so more than 1.000.000 calculations are done to find my the last row that has a value. I chose for this approach because my data isn't consistent, it consists out of 3 columns that all can have some data. To illustrate:

| A          | B           | C            |
|:-----------|------------:|:------------:|
| This       |        This |     This     
|            |      column |          
|            |             |     will     
| be         |          be |      be      
| left       |             |         
|            |             |   aligned

The only assurance I have is that at least one of the 3 columns is filled with data. Is it possible to write a function in VBA that starts from the top and checks if A or B or C is filled, if it isn't, the previous row should be returned? (Without a lot of code?)

Upvotes: 0

Views: 3892

Answers (4)

user9796893
user9796893

Reputation: 1

Taken from John Walkenbach's book 'Excel 2007 Power Programming with VBA' try:

Nextrow = cells(rows.count, 1).end(xlup).row +1

Upvotes: 0

Lukas Ljungstrom
Lukas Ljungstrom

Reputation: 67

Seems as though you already have some answers that work. I'll throw my hat in the ring just for the sake of variety.

Sub test()
Dim Count As Long
Dim A As String
Dim B As String
Dim C As String
With Sheets("Sheet1")
Range("D1").Select
For Count = 1 To 1000000
A = .Cells(Count, "A")
B = .Cells(Count, "B")
C = .Cells(Count, "C")

combined = A & B & C
.Cells(Count, "D").Value = combined
DoEvents
If combined = "" Then GoTo Line100
Next
End With

Line100:

End Sub

This macro stops at the last row that does not have any values in column A, B, or C.

Upvotes: 0

user3598756
user3598756

Reputation: 29421

edited to handle non contiguous ranges

you can try this function:

Function RangeLastRow(rng As Range) As Long
    If WorksheetFunction.CountA(rng) > 0 Then
        With Intersect(rng.SpecialCells(xlCellTypeConstants).EntireRow, rng).EntireRow
            RangeLastRow = Split(.Areas(.Areas.Count).Address, "$")(2)
        End With
    End If
End Function

which will return:

  • zero, if passed range has empty cells only

  • row index of not empty "farthest" cell, if passed range has at least one not empty cell

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

Maybe something like this, find last row per column and compare if it's the LastRow with highest value.

Option Explicit

Sub FindLastRow()

Dim col As Integer
Dim LastRow As Long
Dim MaxLastRow As Long

MaxLastRow = 2 ' initialize value

' modify "Sheet1" to your sheet's name
With Sheets("Sheet1")
    ' loop from column A to Column C >> can modify easily
    For col = 1 To 3
        LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
        If MaxLastRow < LastRow Then MaxLastRow = LastRow
    Next col
End With

End Sub

Upvotes: 2

Related Questions