Avoid For Each Loop

[daniel22bg]

Hi There,

I have a range of about 500 cells, some of which are empty and others are numbers. I would like to divide all the numbers with VBA by 100 without affecting the empty cells. Now i have the following Loop, which takes awfully lot of time. Can I avoid these loops somehow, so that I can optimize my code? Thanks a lot

Dim Rng As Range               
 
For Each Rng In Sheets("1").Range("A1:A500"))
          If Not (IsEmpty(Rng)) Then
                 Rng.Value = Rng.Value / 100
          End If
Next Rng

 

[ChrisM]

I'm sure there's a better answer, but multiplying by .01 should be faster than dividing by 100. Division is one of the slower of the math functions in programming.

And then you can get rid of the IsEmpty test.

 

[Richard Schollar]

Hi

The PasteSpecial method and the SpecialCells method are what you need:

Dim r As Range

'first write 100 into an empty cell:

Cells(1,"IV").Value = 100
Cells(1,"IV").Copy

On Error Resume Next
Set r = Sheets("1").Range("A1:A500").SpecialCells(xlCellTypeConstants,xlNumbers)
On Error Goto 0
If Not r Is Nothing Then
  r.PasteSpecial xlPasteValues,xlPasteSpecialOperationDivide
Else
  'no number found - do something else
End if

'clean up:
Cells(1,"IV").Clear

 

[daniel22bg]

Thanks a lot guys. That help quite a lot