CODEWITHSUNDEEP
excelvba
Alister M
Alister M

Reputation: 57

Copy last value in table column, NOT empty table rows below it

I have a script that is used to find the last entry in a particular table column across different sheets and paste them into a master sheet list. It's operating as "find last used row in column B" but evidently table rows, even if they're blank, count as used rows and so most of my returns are blank because the actual value I'm looking for was entered at the top of the table instead of in the last row. It's also important to note that there are multiple stacked tables on these sheets, but each table only ever has ONE value in the B column, and I'm only ever looking for the most recent one.

Is there a way to tell excel not to count blank table rows as used rows? I'm only interested in returning the last value in column B, even if it may not be the last table row.

Dim rng As Range
Dim cell As Range
Dim result As String
Dim result_sheet As Long
Dim LastRow As Long


Set rng = Range("A2:A200")

For Each cell In rng

result = cell.Value

LastRow = Worksheets(result).Cells(Worksheets(result).Rows.Count, "B").End(xlUp).row
cell.Offset(0, 1).Value = Worksheets(result).Range("B" & LastRow).Value

Next cell

Upvotes: 1

Views: 212

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Here is one way

Sub Sample()
    Dim ws As Worksheet
    Dim tmplRow As Long, lRow As Long, i As Long

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        tmplRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For i = tmplRow To 1 Step -1
            If Len(Trim(.Range("B" & i).Value)) <> 0 Then
                lRow = i
                Exit For
            End If
        Next i

        If lRow = 0 Then lRow = 1

        MsgBox "The last row is " & lRow
    End With
End Sub

Screenshot

enter image description here

And another way

Sub Sample()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lRow As Long, endRow As Long, startRow As Long, i As Long

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Change this to the relevant table number
        Set tbl = .ListObjects(1)

        endRow = tbl.Range.Rows.Count
        startRow = tbl.Range.Row

        For i = endRow To startRow Step -1
            If Len(Trim(.Range("B" & i).Value)) <> 0 Then
                lRow = i
                Exit For
            End If
        Next i

        MsgBox lRow
    End With
End Sub

Upvotes: 1

Related Questions