0

I currently have a table ("Table2") set up on sheet1. In column A, I have row numbers listed all the way down the table to 51, and column B empty to be filled in. I am trying to get the last row used in column B that is not empty.

I've tried using Worksheets("Sheet1").Range("B52").End(xlUp).Row however will still give me a answer of 51 even though the last item filled in within column B is row 3.

I have tried last row functionality but does not seem to work while being used within a table.

R3uK
  • 14,417
  • 7
  • 43
  • 77
Brad
  • 1,450
  • 2
  • 16
  • 37
  • Did you try the find method in the link I provided? – Scott Craner Mar 13 '17 at 13:51
  • @ScottCraner : it wasn't cleat in the initial OP, but the issue is that it always return the last row of the table (excel object), and from memory, that case is not treated in Siddharth's post! ;) – R3uK Mar 13 '17 at 13:55

3 Answers3

3

Use Find it is more reliable with tables:

With Worksheets("Sheet1")
    Debug.Print .Range("B:B").Find(What:="*", _
                      After:=.Range("b1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
End With

enter image description here

Here is one that is not covered in the link, the use of The worksheet function MATCH:

If the Column is text:

With Worksheets("Sheet1")
    Debug.Print Application.WorksheetFunction.Match("ZZZ", .Range("B:B"))
End With

If the column is numbers:

With Worksheets("Sheet1")
    Debug.Print Application.WorksheetFunction.Match(1E+99, .Range("B:B"))
End With
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
1

Here is a good explanation about this : 

With Worksheets("Sheet1")
    Debug.Print .Range("B" & .Rows.Count).End(xlUp).Row
End With 'Worksheets("Sheet1")

As this seems to be inside of an Excel Table Object, try this :

With Worksheets("Sheet1").Range("Table2")
    Debug.Print .Range("B" & .Rows.Count).End(xlUp).Row
End With 'Worksheets("Sheet1").Range("Table2")
Graham
  • 7,431
  • 18
  • 59
  • 84
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Still 51. Could this be because cells A1-C1 is merged? Cells B2, B3, have items in it, Cells B4:B52 do not. – Brad Mar 13 '17 at 13:46
  • @Brad : Could you add a screenshot of your sheet? Maybe you need to specify which workbook you are using, because that method is pretty reliable (except for merged cells, were the data is only in the 1st cell, i.e. here A1-C1 wouldn't be detected) – R3uK Mar 13 '17 at 13:49
  • I think it might be because of the table. I've deleted all of column A, still same issue. The table ranges from A2:C52 named "Table2", however even though most of column B is empty, it still gives back the last row of the table. – Brad Mar 13 '17 at 13:52
  • @Brad : Yup, I just understood that, give the edit a try! ;) – R3uK Mar 13 '17 at 13:53
  • The table range seems to work better when "- .Cells(1,1).Row" is removed. Thank you for working through this issue with me! I really appreciate it! – Brad Mar 13 '17 at 13:59
  • @Brad : My bad, I added it thinking about something else! ;) Anyway, glad I could help! ;) – R3uK Mar 13 '17 at 14:02
0

You can build a simple loop.

Dim lastrow As Long
lastrow = 0
Dim checkrow As Long
For checkrow = 1 To 51
    If Worksheets("Sheet1").Range("B" & checkrow).Value <> "" Then
        lastrow = checkrow
    Else
        'do nothing
    End If
Next checkrow

This will go through all your rows from top to bottom and every time it finds a non-empty value, it will save the number. So when the loop ends, lastrow is last row saved, e.g. last row containing any value (or it remains 0 if no rows contain value).

Also you might want to check if your cell B51 really contains nothing. It might contain some whitespaces and look as empty while not being detected as such.

jacek_wi
  • 455
  • 1
  • 6
  • 20