Looping thru cells that actually have data

[VenturSum]

Hi,

Excel 2007 has a LOT of cells. The looping code (below) looks at all cells, and takes forever...

How can the loop be constrained to consider ONLY cells that have some data??
e.g.: text, formula, hyperlink, notes, comments, etc. AND ignore formatting

- Could .SpecialSells() be used?
- The backend Excel tables, only holds cells with data, could only those cells that have data be evaluated?

A test could be done inside the loop (IF the cell was null, goto the next cell) But this still takes processor time to evaluate.



Bad Code

Dim C as Range, ws as worksheet

For each c in ws.cells
' Do something
Next c

Any thoughts would be greatly appreciated.

John

 

[VoG]

How about

For Each C In ws.UsedRange
' Do something
Next C

and check for blanks within the loop.

 

[VenturSum]

I have no idea where in the worksheet there is any data.
I could use some evaluation for last row and last cell.
but that still evaluates cells that have no data.

I'm wondering if it's possible to consider ONLY those cells that have data within this loop or any other looping syntax.

Thanks,
John

 

[VenturSum]

VOG,
I am unfamiliar with .UsedRange.
what does it do?

John

 

[wigi]

UsedRange contains the cells with are used on a sheet. Also the cells between non-empty cells are part of it.

For instance: A1:Q30

or

E2:Q30

The range returned has a rectangular shape.

 

[VoG]

To illustrate, in a blank sheet, enter some data, preferably not starting at A1. Then run

Sub ventur()
ActiveSheet.UsedRange.Select
End Sub

 

[VenturSum]

WOW!

.UsedRange really speed things up.
But it also evaluates cells that have no data.
So I'm still wondering.. Is it possible to evaluate only cells that have data?

what about .SpecialCells()?

Any thoughts???
John

 

[Norie]

What are you actually doing with the cells?

 

[VoG]

Perhaps

Dim c As Range, r As Range
With ActiveSheet.UsedRange
    Set r = Union(.SpecialCells(xlCellTypeConstants), .SpecialCells(xlCellTypeFormulas))
End With
For Each c In r
'do something
Next c

 

[VenturSum]

VOG - I'll give that a try, but I have to go now.

Norie - What am I doing?

I need to create a table that has all the text in the workbook.
The end product would be a couple columns

Address     Text
A1            Welcome
A2            Some text
D3            Some more Text
F7            = F6 + F5
FF34         Text not expected cause it's too far to scroll

So I need to evaluate all the cells in worksheet that actually have data.
and I don't know how big the worksheets are.

Thanks, John