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

 

[Norie]

John

Is there absolutely no structure to the data you will have to work with?

 

[VenturSum]

Norie - Correct, NO Structure, as I don't know what the worksheets will look like. The boss just want to see what's written. So I need to pull all the text and keep track of where it came from.

 

[VenturSum]

But this still begs the question.

Is it possible to evaluate ONLY those cells that actually have data.

We know that in the back-end tables of Excel, Excel only tracks the data. It is displayed in the familiar worksheet. So is it possible to use those back tables for the evaluation??

John

 

[VoG]

Did you miss this? http://www.mrexcel.com/forum/showpost.php?p=2319864&postcount=9

 

[Norie]

Is the data non-contiguous and all over place?

eg the first piece of data might be in A1, the next in D75, the next in Z100.

 

[Norie]

John

What back-end tables are you referring to?

Are there other files involved?

 

[VenturSum]

Norie - Yes, the data must be considered non-contiguous, because I don't know where it is. -

VoG,
I tried the following code, but it returned nothing.
I'm showing your structure incorporated into my code just incase I missed something.

Sub LoopTest3()

Dim c As Range, r As Range
Dim ws As Worksheet
Dim sData As String, sAddress As String
    
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Set ws = Worksheets("Sheet1")
    ws.Select
    
    With ActiveSheet.UsedRange
        Set r = Union(.SpecialCells(xlCellTypeConstants), _
                      .SpecialCells(xlCellTypeFormulas))
    End With
    
        '-- Loop Thru Cells ---------------------------------
        For Each c In r
            sData = c.Value
            sAddress = c.Address(False, False, xlA1)
Debug.Print sAddress & " " & sData
        Next c
        
    Application.ScreenUpdating = True
    Set ws = Nothing
        
End Sub

Thank you,
John

 

[VenturSum]

GOT IT!.. Thanks to VoG..

So far, I can't evaluate all special cells using the Union. but I can evaluate individual types of special cell...

Sub LoopTest4()

Dim c As Range, r As Range
Dim ws As Worksheet
Dim sData As String, sAddress As String
    
    Application.ScreenUpdating = False
    On Error Resume Next
    
    Set ws = Worksheets("Sheet1")
    ws.Select
    
    With ActiveSheet.UsedRange
        Set r = .SpecialCells(xlCellTypeConstants)

        'Use other special cells for Formula and comments.
        'This worked but the above Union() did not work -- for me that is.

    End With
    
        '-- Loop Thru Cells ---------------------------------
        For Each c In r
            sData = c.Value
            sAddress = c.Address(False, False, xlA1)
Debug.Print sAddress & " " & sData
        Next c
        
    Application.ScreenUpdating = True
    Set ws = Nothing
        
End Sub

 

[VenturSum]

Final solution:

Don't use Union() just commas.

    With ActiveSheet.UsedRange
        Set r = .SpecialCells(xlCellTypeConstants, xlCellTypeFormulas)
    End With

John

 

[VenturSum]

That doesn't work
the comma denotes a second variable for value.

Jumped the gun without test.

However this does work:

    Set ws = Worksheets("Sheet1")
    ws.Select
    
        '-- Loop Thru Cells ---------------------------------
        For Each c In ws.Cells.SpecialCells(xlCellTypeConstants)
            sData = c.Value
            sAddress = c.Address(False, False, xlA1)
Debug.Print sAddress & " " & sData

        Next c

</pre>
John