How to get used row count for a partial sheet 3

[mscallisto]

Normally to get a "used rows" count for a sheet I use the standby code

usedrows = ActiveSheet.UsedRange.Rows.Count

While trying to get the "used rows count" for one or more (but not all) columns within a sheet, I tried:

usedrows = ActiveSheet.UsedRange.columns("t:t").Rows.Count

It doesn't compute, is there a way to do this?

 

[sahmiele]

This will get you a count.


colrows = 0
usedrows = ActiveSheet.UsedRange.Rows.Count
myrange = "a1:a" & usedrows
For Each a In Range(myrange)
If a.Text <> "" Then colrows = colrows + 1
Next a
MsgBox (colrows)

 

[mscallisto]

Thanks sahmiele

Thats similar to the code I currently use but I hoped
there was an easier method using only one line of code like a variation of

[b]usedrows = ActiveSheet.UsedRange.Rows.Count[/b]

 

[vladk]

mscallisto,

I couldn't find decent solution. But you could look at the Intersect method.

 

[SkipVought]

Hi,

There are pitfalls in stacking QueryTables on the same sheet in the same column.

I'd recommend using ADO and the CopyFromRecordset method.

Each time thru the loop you calculate the last used cell.

I like to use...

  lLastRow = [A1].CurrentRegion.Rows.Count + 1

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[AnotherHiggins]

If you're after the count for a single column, then how about:

[t65536].end(xlup).row

NOTE: The next release of Excel will have over 1 million rows, so this code might become outdated.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

...then to accomodate that event...

Cells(Cells.rows.count, "T").end(xlup).row

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[mscallisto]

Thanks Skip and John...thats exactly what I wanted!!

usedrows = Cells(Cells.Rows.Count, "M").End(xlUp).Row works perfectly yet I still don't know why my code didn't work.

usedrows = ActiveSheet.UsedRange.columns("t:t").Rows.Count ???

not that I really need to know how dumb I was to try it!

sam

 

[xlbo]

Have a look at this FAQ as to why your UsedRange method may not work:

faq707-2115

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mscallisto]

Thanks Geoff I'll paste that thread on my wall.

 

[firefytr]

Whew! Thanks for that Skip. Going through this thread I was starting to get scared! LOL!

And to ensure we appease vladk (j/k vladk) make sure we explicitly reference out objects ..

Sheets("Sheet1").cells(sheets("Sheet1").cells.rows.count, "T").end(xlup).row

-----------
Regards,
Zack Barresse