usedrange problem

[andrew299]

I am having a problem with usedrange
I want to count the number of cells in a column for the purposes of a loop
I am using

ActiveSheet.UsedRange.Select
cellcount = Selection.Rows.Count


The trouble is the usedrange seems to pick up cells where there is no data. There may have been in the past but there is none there anymore. This sends the loop out of sync because too many rows are being counted.
Is there a way of refreshing the sheet to remove this 'ghost' data?

Andrew299

 

[xlbo]

Please see about a million threads on this topic plus at least 2 FAQs in this forum (from myself & Loomah)

If you really want to use UsedRange, use

Activesheet.usedrange.rows.count
LRow = activesheet.usedrange.rows.count

Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur