CODEWITHSUNDEEP
excelvba
carlos_cs
carlos_cs

Reputation: 124

Find last row of a specific range of columns

I have a table that ranges from "A:EV".

I want to find the last row of only range "A:DD".

The columns might have blank cells, so I need to go through all and find the furthest row of columns A to DD.

How can I code it?

Upvotes: 0

Views: 3224

Answers (3)

Regit
Regit

Reputation: 1

And with a little voodoo, if you are interested in also getting the column where the last row was found but don't want to extract it from .Address, use this.

sub voodoo()

    Dim theresult As Variant
    With Sheets("Sheet9") 'Change to your sheet
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            Set theresult = .Range("Y:DD").Find(What:="*", _
                      After:=.Range("Y1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False)
    Else
        'Used 1000 to prove that it was not defaulting to this.
        'Change to 1 when using in actual code.
        lastrow = 1000 'Change to 1 when using. 
    End If
    MsgBox ("LastRow " & theresult.Row & " column " & theresult.Column)
End With
End Sub

Upvotes: 0

Regit
Regit

Reputation: 1

And to adjust the range a bit, change the two column letters in these two lines.

This one searches over columns A:DD.

lastrow = .Range("A:DD").Find(What:="*", _
                      After:=.Range("A1"), _

This version narrows the search range to Y:DD

lastrow = .Range("Y:DD").Find(What:="*", _
                      After:=.Range("Y1"), _

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

Modified from HERE

Sub foo()


With Sheets("Sheet9") 'Change to your sheet
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Range("A:DD").Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        'Used 1000 to prove that it was not defaulting to this.
        'Change to 1 when using in actual code.
        lastrow = 1000 'Change to 1 when using. 
    End If
    MsgBox lastrow
End With
End Sub

enter image description here

Upvotes: 1

Related Questions