Hello all,
I currently am writing a macro that is supposed to count the number of used rows in a table for several different tables and adds them up to determine the size of another table which will consolidate the individual tables' data. The code currently looks like this:
Dim i, TotalRows as Long
TotalRows = 0
for i = 1 to ActiveSheet.ListObjects.Count
TotalRows = ActiveSheet.Listobjects(i).DataBodyRange.Rows.Count + TotalRows
next i
Here is the issue - the tables contain several extra empty rows for users to input data into. There is a formula in one column of the table that autofills to all rows, including any rows that are left empty. These empty rows need to remain in the table in case users need to add additional data later. Because of this formula, the .DataBodyRange property is returning all rows including the ones the user leaves blank. When I consolidate the data in the tables into one table, it would then include these blank rows.
Is there any way I can point directly towards a desired column's used range - for instance, one that the user will always have to manually fill? My thinking is something like this, but it doesn't work as intended:
TotalRows = Worksheet.Listobjects(i).ListColumns(1).DataBodyRange.Rows.Count + ResourcesRows
I am aware that I can loop through the values in the tables' desired column individually until I encounter an empty cell, but I am wondering if there is a cleaner approach that will utilize the table's properties. Any help is greatly appreciated.
Bookmarks