VBA - adjust rows in active table

JamesonMH

Board Regular
Joined
Apr 17, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I've searched the net and mrexcel.com for this with no luck for my specific (simple) problem. Macro recorder hasn't helped me either. I'm just starting out with VBA...

I have three tables in my active sheet. Table 3 (the one I need solution for) has its headers from G8:J8. That stays fixed. However, the rows change based on the amount of data.

I'd like Table 3 to Resize its rows to the last row of data, so there are not tons of blank Table rows still underneath.

I already spent a while editing this larger macro to get the data how I need it. This row adjustment is the final touch I need.

Thanks,
James
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
How about something like this. Please note what you are calling "Table 3", I am calling "Table3" in the code...

Code:
Sub tbllastrow()


    Dim tbl As ListObject
    Dim lRow As Long, tbllrow As Long
    Dim rng As Range
    Dim adr As String
    
    Set tbl = ActiveSheet.ListObjects("Table3")
    lRow = tbl.DataBodyRange(1, 1).End(xlDown).Row
    adr = tbl.HeaderRowRange.Address
    tbllrow = lRow - Range(adr).Row + 1
    Set rng = Range("Table3[#All]").Resize(tbllrow, 4)
    ActiveSheet.ListObjects("Table3").Resize rng
    
End Sub
 
Upvote 0
How about something like this. Please note what you are calling "Table 3", I am calling "Table3" in the code...

Code:
Sub tbllastrow()


    Dim tbl As ListObject
    Dim lRow As Long, tbllrow As Long
    Dim rng As Range
    Dim adr As String
    
    Set tbl = ActiveSheet.ListObjects("Table3")
    lRow = tbl.DataBodyRange(1, 1).End(xlDown).Row
    adr = tbl.HeaderRowRange.Address
    tbllrow = lRow - Range(adr).Row + 1
    Set rng = Range("Table3[#All]").Resize(tbllrow, 4)
    ActiveSheet.ListObjects("Table3").Resize rng
    
End Sub

Thanks igold, that worked perfectly! I know this is asking a lot so no worries if you don't have time...but any chance you could describe roughly what this code is doing so I can work with it in the future?

Cheers,
James
 
Upvote 0
You're welcome. I was happy to help. Yes, I will put comments in the code. It may not be until later tonight or tomorrow as I am just about to run out...
 
Upvote 0
Here is the code with comments. I also added a second code which uses the HeaderRow of the table to find the last row with data, instead of using the DataBodyRange of the table. They both work.

You may want to check out this link. It is a very good guide to using Tables. https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Code:
Sub tbllrow()


    Dim tbl As ListObject
    Dim lRow As Long, tbllrow As Long
    Dim rng As Range
    Dim adr As String
    
    Set tbl = ActiveSheet.ListObjects("Table3")
    
'   Using the first cell (not the header row) in the first column of the table,
'   get the worksheet row number of the last row of the table with data.
'   Same as using Ctrl + Down Arrow
    lRow = tbl.DataBodyRange(1, 1).End(xlDown).Row
    
'   Get the address of the Header Row of the table
    adr = tbl.HeaderRowRange.Address
    
'   Using the address of the HeaderRow, determine the worksheet row the table
'   starts on. Subtract the last row of data from above and you know how many
'   rows are in your table.
    tbllrow = lRow - Range(adr).Row + 1
    
'   Resize the table according to how many rows (tbllrow) and your set number
'   of columns (4)
    Set rng = Range("Table3[#All]").Resize(tbllrow, 4)
    ActiveSheet.ListObjects("Table3").Resize rng
    
End Sub


Sub tbllrow2()


    Dim tbl As ListObject
    Dim lRow As Long, tbllrow As Long
    Dim rng As Range
    Dim adr As String
    
    Set tbl = ActiveSheet.ListObjects("Table3")
    
'   Using the HeaderRow get the worksheet row number of the last row of the table with data.
'   Same as using Ctrl + Down Arrow
    lRow = tbl.HeaderRowRange.End(xlDown).Row
    
'   Get the address of the Header Row of the table
    adr = tbl.HeaderRowRange.Address
    
'   Using the address of the HeaderRow, determine what worksheet row the table
'   starts on. Subtract the last row of data from above and you know how many
'   rows are in your table
    tbllrow = lRow - Range(adr).Row + 1
    
'   Resize the table according to how many rows (tbllrow) and your set number
'   of columns (4)
    Set rng = Range("Table3[#All]").Resize(tbllrow, 4)
    ActiveSheet.ListObjects("Table3").Resize rng
    
End Sub
I hope this helps.
 
Upvote 0
Here is the code with comments. I also added a second code which uses the HeaderRow of the table to find the last row with data, instead of using the DataBodyRange of the table. They both work.

You may want to check out this link. It is a very good guide to using Tables. https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Code:
Sub tbllrow()


    Dim tbl As ListObject
    Dim lRow As Long, tbllrow As Long
    Dim rng As Range
    Dim adr As String
    
    Set tbl = ActiveSheet.ListObjects("Table3")
    
'   Using the first cell (not the header row) in the first column of the table,
'   get the worksheet row number of the last row of the table with data.
'   Same as using Ctrl + Down Arrow
    lRow = tbl.DataBodyRange(1, 1).End(xlDown).Row
    
'   Get the address of the Header Row of the table
    adr = tbl.HeaderRowRange.Address
    
'   Using the address of the HeaderRow, determine the worksheet row the table
'   starts on. Subtract the last row of data from above and you know how many
'   rows are in your table.
    tbllrow = lRow - Range(adr).Row + 1
    
'   Resize the table according to how many rows (tbllrow) and your set number
'   of columns (4)
    Set rng = Range("Table3[#All]").Resize(tbllrow, 4)
    ActiveSheet.ListObjects("Table3").Resize rng
    
End Sub


Sub tbllrow2()


    Dim tbl As ListObject
    Dim lRow As Long, tbllrow As Long
    Dim rng As Range
    Dim adr As String
    
    Set tbl = ActiveSheet.ListObjects("Table3")
    
'   Using the HeaderRow get the worksheet row number of the last row of the table with data.
'   Same as using Ctrl + Down Arrow
    lRow = tbl.HeaderRowRange.End(xlDown).Row
    
'   Get the address of the Header Row of the table
    adr = tbl.HeaderRowRange.Address
    
'   Using the address of the HeaderRow, determine what worksheet row the table
'   starts on. Subtract the last row of data from above and you know how many
'   rows are in your table
    tbllrow = lRow - Range(adr).Row + 1
    
'   Resize the table according to how many rows (tbllrow) and your set number
'   of columns (4)
    Set rng = Range("Table3[#All]").Resize(tbllrow, 4)
    ActiveSheet.ListObjects("Table3").Resize rng
    
End Sub
I hope this helps.

This is so helpful. I really appreciate you taking the time to detail the code - thanks igold!

The link you provided looks great so far...it might even save me a few posts on mrexcel. :LOL:
 
Upvote 0
Great, I am glad all is working for you.

Interesting Delray connection.

Regards,

igold
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top