Get the row count of different areas in the Excel

[Curious]

Hi,

I have a sheet (renamed as "LongTermLimits") that contains two areas:

Area 1: column A-column H, there are certain number of rows;

Area 2: column P-column T, there are certain number of rows.

I was advised to use the following code to get the row count for the
entire sheet:

myrowcount = Worksheets("LongTermLimits").UsedRange.Rows.Count

How can I get the row count for each area? Thanks!

 

[JLatham]

Assuming in each area, the columns will have the same number of rows used,
even though each area may use a different number of rows than the other one.

Dim Area1 As Long
Dim Area2 As Long
Area1 = Worksheets("LongTermLimits").Range("A" & Rows.Count).End(xlUp).Row
Area2 = Worksheets("LongTermLimits").Range("P" & Rows.Count).End(xlUp).Row

Those will give you the row number that each ends on. You may need to do a
little more math to determine the actual number of rows of data. Assume that
you have labels in row 1 in both areas. Then the number of data rows is
Area1-1 or Area2-1.

..End(xlUp). starts at a given cell and looks UP the sheet until it finds the
end of cells with similar content (or lack of content) as the cell
referenced. Since we are using Rows.Count as the row number to start looking
from, we are looking from the very bottom of the sheet and we are assuming it
is an empty cell. The row returned will be the first one it encounters with
a value or formula in it; or put another way, the last row in that column
that is actually used.

 

[Curious]

Thanks. This is helpful.