Using CurrentRegion to count contiguous rows 
Author Message
 Using CurrentRegion to count contiguous rows

I'm using the following line of code to determine the number of rows in a
spreadsheet:

ColumnRange = "A"
ColumnCellNo = 2

NRows = Sheets(Table).Range(ColumnRange & ColumnCellNo) _
        .CurrentRegion.Rows.Count

I only want it to count the rows in column A.  Since column C has more
rows of data, the above formula is returning the value for column C.  How
do I limit the formula to returning only the value for column A?

Barney Byrd



Sat, 10 Jul 2004 04:01:10 GMT  
 Using CurrentRegion to count contiguous rows
If you're trying to count the number of rows in A2:Axx (until the gap), couldn't
you exclude the current region and do xldown like:

Dim myrange As Range
Dim myaddr As String
Dim ColumnRange As String
Dim ColumnCellNo As Long

ColumnRange = "A"
ColumnCellNo = 2
myaddr = ColumnRange & ColumnCellNo

Set myrange = Sheets(Table).Range(Range(myaddr), _
                    Range(myaddr).End(xlDown))

MsgBox myrange.Rows.Count

===
But you should add some checking to see if A2 and A3 are filled/empty, so the
xldown works ok.

====

If you're find the last cell used in column A, maybe something like:

set myrange = range("a2",cells(rows.count,"a").end(xlup))
msgbox myrange.rows.count

Quote:

> I'm using the following line of code to determine the number of rows in a
> spreadsheet:

> ColumnRange = "A"
> ColumnCellNo = 2

> NRows = Sheets(Table).Range(ColumnRange & ColumnCellNo) _
>         .CurrentRegion.Rows.Count

> I only want it to count the rows in column A.  Since column C has more
> rows of data, the above formula is returning the value for column C.  How
> do I limit the formula to returning only the value for column A?

> Barney Byrd


--

Dave Peterson



Sat, 10 Jul 2004 10:16:19 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Currentregion.rows.count makes an error ?

2. Counting Non-Contiguous Group of Rows

3. Counting non-contiguous cells only if there is a number >0 in them

4. CurrentRegion used in a Function ?

5. Excel 2000: sorting using CurrentRegion

6. Non-Contiguous Cells in Same Row

7. Count unique and Assign unique items in a row to variables using VBA

8. Count visible rows when using auto filter

9. Hiding non-contiguous rows

10. NON Contiguous Rows


 
Powered by phpBB® Forum Software © phpBB Group