By carlos_cs


2016-05-06 16:16:27 8 Comments

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?

3 comments

@Regit 2018-12-19 17:38:31

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

@Regit 2018-12-19 17:21:21

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"), _

@Regit 2018-12-19 17:27:39

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

@Scott Craner 2016-05-06 17:35:42

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

@Forward Ed 2016-05-06 18:08:33

Else lastrow= 1000? shouldn't that be 1 for an empty worksheet? Assuming that is the only time you would fall into the else statment

@Scott Craner 2016-05-06 18:12:47

@ForwardEd yes but I wanted to show that it was not defaulting to the else. I will change it and put a note.

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Excel VBA Find last row in range

13 Answered Questions

[SOLVED] How can I find last row that contains data in a specific column?

  • 2008-09-16 10:54:53
  • Lipis
  • 338175 View
  • 59 Score
  • 13 Answer
  • Tags:   excel vba

4 Answered Questions

[SOLVED] Find the last not empty row in a range of cells holding a formula

2 Answered Questions

Finding the last row within a Specific Range

4 Answered Questions

[SOLVED] Return a range from A1 to the true last used cell

2 Answered Questions

[SOLVED] How to select a range of the second row to the last row

  • 2013-06-26 17:47:34
  • rwbarg15
  • 156517 View
  • 6 Score
  • 2 Answer
  • Tags:   excel-vba vba excel

3 Answered Questions

[SOLVED] VBA Excel sort range by specific column

4 Answered Questions

[SOLVED] Excel VBA - selecting range to last completely blank row

2 Answered Questions

[SOLVED] how to use variable "last row number" to set the range in another column

  • 2013-10-16 17:19:46
  • user2887213
  • 31087 View
  • 1 Score
  • 2 Answer
  • Tags:   excel-vba vba excel

1 Answered Questions

[SOLVED] Find Row Macro Clarification

  • 2014-05-24 19:26:18
  • CSSWormy
  • 114 View
  • 5 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

Sponsored Content