Dynamically Resizing Table to last row?

user_4021184 0
Viewed 6446

I have a table that I would like to resize dynamically in VBA. My current code is this:

Sub resizedata()
Dim ws As Worksheet
Dim ob As ListObject
Dim Lrow1 As Long

Lrow1 = Sheets("Sheet4").Cells(Rows.Count, "J").End(xlUp).Row
Set ws = ActiveWorkbook.Worksheets("Sheet4")
Set ob = ws.ListObjects("Table28")

ob.Resize ob.Range.Resize(Lrow1)

End Sub

I would like to add one condition onto this though...

  1. The table should resize to before the first 0 in column J.

For instance:

+-------+--------+-------+
|Date(I)|Hours(J)| Sal(K)|
+-------+--------+-------+
| Aug   | 150000 | 12356 |
| Sep   |  82547 |  8755 |
| Oct   |  92857 | 98765 |
| Nov   |  10057 | 45321 |
| Dec   |      0 |     0 |
| Jan   |      0 |     0 |
+-------+--------+-------+

The above table's last row should be the November row because December is the first 0 value in column J.

Can anyone assist in revising my existing code?

excel-2010 vba excel
3 Answers

This should help you to automatically resize the table.

   With Worksheets("sheet-name").ListObjects("table-name")
        .Resize .Range(1, 1).CurrentRegion
    End With
user_111200000
Related