"For Each cell In Selection" - question

[gms4b]

Hi, 

I have the following code which essentially applies number formatting for each cell depending on the size of the number (essentially setting 3 significant figures). I initially had it apply to the whole sheet, but changed it to just a selection (highlight/select a table within a sheet). Works great.

But, when I apply it to a whole column (i.e. clicking on a column letter), it either takes alot of time, or dies/crashes. I assume that its looking down all the way to row 1048576, lol. Is there a way to modify this so when I click on column it only looks at cells with values? or ignore blank cells?

Any help appreciated.

Cheers,

Greg

Sub SignificantFigures_v1()

'For Each cell In ActiveSheet.UsedRange.Cells
'this will apply to the whole sheet

For Each cell In Selection
'this will apply just to selected cells

If IsNumeric(cell) = False Then
'do nothing
ElseIf cell.Value >= 10000 Then
cell.NumberFormat = "00000"
ElseIf cell.Value >= 1000 Then
cell.NumberFormat = "0000"
ElseIf cell.Value >= 100 Then
cell.NumberFormat = "000"
ElseIf cell.Value >= 99.95 Then
cell.NumberFormat = "000"
ElseIf cell.Value >= 10 Then
cell.NumberFormat = "00.0"
ElseIf cell.Value >= 1 Then
cell.NumberFormat = "0.00"
ElseIf cell.Value >= 0.1 Then
cell.NumberFormat = "0.000"
ElseIf cell.Value >= 0.01 Then
cell.NumberFormat = "0.0000"
ElseIf cell.Value >= 0.001 Then
cell.NumberFormat = "0.00000"
ElseIf cell.Value >= 0.0001 Then
cell.NumberFormat = "0.000000"
ElseIf cell.Value >= 0.00001 Then
cell.NumberFormat = "0.0000000"
Else
cell.NumberFormat = "0.000000"
End If

Next
End Sub

[#2 · Accepted solution]

try this:
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)

[#3]

@mtarler 

Nice! This seems to be working.  I knew it had something to do with .Usedrange, I just wasn't sure how to put it all together. Thanks!!

Greg