1

I have a worksheet with a table that is constantly being updated, so the number of rows it has is variable. I am interested in retrieving the last value in column B. However, the tricky part is that not all rows in this column of the table have a value. Sometimes the next cell with an actual value could be just below the previous one, and sometimes there could be many empty spaces in between.

I tried to use the following code to do so:

LastValue = Cells(Rows.Count, "B").End(xlUp).Value

The issue I am experiencing happens in a very specific scenario. If we suppose the last cell with a value in the column is B6, but the last cell of the table corresponding to this column is B10, the value retrieved is empty (the one in B10). What I would need to find is cell B6 and give that value to the variable LastValue. Is there any way to do this? I could only find code examples to find the last row of the entire worksheet, ignoring individual columns.

braX
  • 11,506
  • 5
  • 20
  • 33
Kevin Oeda
  • 35
  • 4
  • In this instance, is cell `B10` truly empty, or does it have a space in it? – Steven Pomponio Jul 11 '20 at 05:16
  • You could just find the last row of each column you care about first, and then compare each of those to the others to find the largest row, and then get its value. – braX Jul 11 '20 at 05:21
  • @StevenPomponio All the cells from B7 to B10 are truly empty. I even tried to define the as empty with `Range("B5:B6") = Empty` to test my piece of code but I keep getting B10 as the last row. – Kevin Oeda Jul 11 '20 at 05:21
  • @braX All of the columns have the exact same number of rows, because all of them belong to the same table. They just have empty cells when are not required. That is what I can not figure out. – Kevin Oeda Jul 11 '20 at 05:22
  • Then I do not see the problem... you just get the last row like normal, and then get the value of the column in that row... what am i missing? – braX Jul 11 '20 at 05:30
  • @braX I believe the data is stored as an actual `table` resulting in the last row code above always outputting the row number of the last record, instead of the last cell with a value. I think possibly adding in a photo of the table in this state would help clarify. – Steven Pomponio Jul 11 '20 at 05:32
  • it doesnt matter if it's a "table" - it still has columns and rows. – braX Jul 11 '20 at 05:34
  • @StevenPomponio is right and found the solution. The issue can be better understood with the following image: [Sample Table](https://i.ibb.co/2ngCNCf/Excel.png). The output my code generates is cell `B10` (the orange one), and I need it to identify cell `B6` (the blue one). – Kevin Oeda Jul 11 '20 at 06:31

1 Answers1

0

This code works below based on your worksheet being titled Sheet1 and the table as Table 1.

Sub FindLastRowInExcelTable()

Dim lastrow As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")

lastrow = ws.ListObjects("Table1").Range.Columns(2).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End Sub

You can modify Columns(2) to change the column you are looking to count.

Steven Pomponio
  • 340
  • 2
  • 13