referencing cellswith values in them

[Pjam]

anyone know how to reference cells with values in them, no matter where they are on the spreadsheet?

 

[mvptomlinson]

Not sure if this is what you mean, but in VBA you could use something like this, which would select all cells with constants (text/number/errors/logical values entered manually, not using formulas) in the range A1:Z100:

Range("A1:Z100").SpecialCells(xlCellTypeConstants, 23).Select

Or this, to select all cells with formulas that result in text/numbers/errors/logicals:

Range("A1:Z100").SpecialCells(xlCellTypeFormulas, 23).Select

These perform the same function as using F5-->Special-->Constants or F5-->Special-->Formulas.

 

[Pjam]

no i dontwant to use a range, because the values could be in any cell throughout the spreadsheet, so how do i reference code to any cell in a sheet that has a value in it?

the only thing i have used so far us

For Each c In Sheets("sheet1").UsedRange.Cells<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If Not c = Empty Then<o></o>

 

[mvptomlinson]

Umm, then switch out Range("A1:Z100") with UsedRange

Using SpecialCells will be MUCH quicker than looping through every cell in your sheet.

 

[jindon]

Range("A1:Z100").SpecialCells(xlCellTypeConstants, 23).Select
Range("A1:Z100").SpecialCells(xlCellTypeFormulas, 23).Select

You don't need "23" when you are referring to all types.

 

[mvptomlinson]

Good to know, Jindon. I just copied what Excel's macro recorder spit out. I tested a few others and the number changed, so I left it. Thanks.