How to Count Number of Used Rows in a Specified Column in a Table

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.

Hi. For example:

Please Login or Register  to view this content.

I did a bit more Googling and came up with the following solution:

Dim i, TotalRows as Long

TotalRows = 0

for i = 1 to ActiveSheet.ListObjects.Count

TotalRows = Application.WorksheetFunction.CountA(ActiveSheet.Listobjects(i).ListColumns(1).DataBodyRange) + TotalRows

next i

Please let me know of any better alternatives that you are aware of, I'd be happy to learn more solutions.

You beat me to it by a minute! Thank you kindly!