VBA-dealing with tables with dynamic height(number of rows) and width(number of columns)

excel-logo

VBA-dealing with tables with dynamic height(number of rows) and width(number of columns)

I was doing some excel macro coding for work. I had one of those moment when I knew I knew the code, but it was on the tip of my tongue and I couldn’t think of it.

The code I was trying to remember was determining the width and the height of a table of data in excel. The size of the table could change, so the code had to be dynamic.

I decided to look it up online and found a lot of complex answers with multiple lines of code. Not the 1 line code answers that I usually use.

I did however remember how to do it and decided to post the code I use in the hope it will help someone.

The 1 line of code for number of rows is:
Range(“A” & Rows.Count).End(xlUp).Row

The 1 line of code for the number of columns is:
Cells(1, Columns.Count).End(xlToLeft).Column

A brief explanation of what the code is doing

Range(“A” & Rows.Count).End(xlUp).Row
Rows.Count
Returns the total number of rows in the worksheet.

Range(“A” & Rows.Count) or Range(“A1048576”) after Rows.Count has been calculated
Returns the bottom most cell in column “A” in the worksheet

Range.End Property
Returns a Range object that represents the cell at the end of the region that contains the source range.
What that basically means is that when you type the code Range(“A” & Rows.Count).End(xlUp) it’s the same as selecting the last row in  Column A and pressing END+UP ARROW. Doing so selects the first cell above the last row that is not empty.

Now you have the last row of data you just return the row number of that object.
Range(“A” & Rows.Count).End(xlUp).Row

For columns it’s the same Idea except we use cells because columns in range are represented by a letter, preventing us from doing it.

Cells(1, Columns.Count).End(xlToLeft).Column
Columns.Count
Returns the total number of Columns in the worksheet.

Cells(1, Columns.Count) or Cells(1, 16384) after Columns.Count has been calculated
Returns the right most cell in row 1 in the worksheet

Cells.End Property
Returns a Range object that represents the cell at the end of the region that contains the source range.
What that basically means is that when you type the code Cells(1, Columns.Count).End(xlToLeft) it’s the same as selecting the last column in row 1 and pressing END+LEFT ARROW. Doing so selects the first cell to the left of the last column that is not empty.

Now you have the last row of data you just return the Column number of that object.
Cells(1, Columns.Count).End(xlToLeft).Column

You should make two variables so you only do it once, increasing performance of your code.

Note: make sure you pick rows and columns in you data table that can’t have blank cells. For example the below table. If you ran the row code on column C instead of A it would return a row height of 3. Or if you ran the column code on row 2 it would return a column width of 3. Use rows which are compulsory in the data set. For Columns row 1 is usually fine because it contain the headings. For rows choose a column that is compulsory in the data entry process.  An example might be ID as a user can’t be in the system unless he is given an ID.

  A B C D
1 ID First Name Last Name Address
2 2134 LLoyd McAllister  
3 1233 Tom Jones 123 Fake street
4 3213 Stephen   124 Fake street

If you want to write an excel macro that deals with any size table. Use the above two lines of code to work out the height(number of rows) and width(number of columns).

Leave a Reply

Your email address will not be published. Required fields are marked *