0

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
carlos_cs
  • 114
  • 1
  • 2
  • 10
  • 1
    Same way you check for a single column. Then generate a loop to go from column A to DD checking where the last row. Store the maximum value. [This link](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) should give you a good starting point – Forward Ed May 06 '16 at 16:20
  • 1
    @ScottCraner Have a snickers, you are going blind, that is the same link I posted 8) Better?. – Forward Ed May 06 '16 at 16:27
  • I did not see the edit sorry. On vacation and using my phone. – Scott Craner May 06 '16 at 16:29
  • My problem is also that I get the last row of the table and not with actual data because the table has some blanks in the last rows and it is giving me wrong number. – carlos_cs May 06 '16 at 16:30
  • Then use the FIND() method in the link provided by @ForwardEd . It will find the last row with data not formatting. – Scott Craner May 06 '16 at 16:32
  • @ForwardEd I got the reference. Also it is my wife's family thing so I find solace in doing this. When I can sneak away I get on my laptop for "work" other times I am sitting on my phone. – Scott Craner May 06 '16 at 16:36
  • your answer is in the link provided by @ForwardEd. Use the Range.Find() method. It will do what you want. – Scott Craner May 06 '16 at 16:43
  • I have tried "Find Last Row in a Column" and "Find Last Row in a Sheet" from the link provided. I get error code with the first one and 8 with the second. Both not what I want. – carlos_cs May 06 '16 at 16:53
  • @Scott Craner. For context: My table rows is 8 and the number I want for lastrow is 1, because I only have the headings and some formulas in row2 between DE:EV. – carlos_cs May 06 '16 at 17:06

3 Answers3

1

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

Community
  • 1
  • 1
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    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 – Forward Ed May 06 '16 at 18:08
  • @ForwardEd yes but I wanted to show that it was not defaulting to the else. I will change it and put a note. – Scott Craner May 06 '16 at 18:12
0

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
  • 1
  • 1
  • 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. – Regit Dec 19 '18 at 17:27
0

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
  • 1
  • 1