PDA

View Full Version : Cout used rows withing a range



av8tordude
12-19-2012, 03:32 AM
How can I count the number of rows used starting from row 9 to row 10009 and within columns A - AB

patel
12-19-2012, 04:50 AM
Please, explain better the scenario, attach a sample file with 20 rows

av8tordude
12-19-2012, 04:58 AM
I use rows 1 - 10009, but I only want to count the number or rows that are used between 9 - 10009. Also, because I use columns AC thru BS to store information, I don't want to the code to used these columns to check if the rows are used. That is why I only want it to check columns A - AB.

Bob Phillips
12-19-2012, 06:44 AM
ARe all of A:AB populated, or will it be sparse?

av8tordude
12-19-2012, 08:03 AM
A:AB will be sparsed. The only column that will eventually have all rows filled will be Column A

david000
12-19-2012, 01:40 PM
If there really is a column that is always populated then you can use some formulas to help keep track of their number in real time. If you moved everything over one column and had a helper column you can keep a running total of non-blank cells in column B, then use another formula to return that total number in cell A1 above the running count of non-blanks.


Sub Get_Last()
Dim lr As Long
Dim rng As Range
lr = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Range("A2").Resize(lr - 1)
rng = "=IF(RC[1]<>"""",COUNTA(R2C[1]:RC[1]),"""")"
Range("a1").Formula = "=LOOKUP(99^99,A:A)"
End Sub


Take a blank worksheet and put some data every other row or so in column B then run this.