CODEWITHSUNDEEP
excelvba
EmberZ
EmberZ

Reputation: 109

VBA Alternative to For Each

I am currently using a lot of For ... Each loops in my code which is slowing it down a lot, is there a faster approach?

I have heard I could copy the range to an array and edit the array and paste it back but im having a few problems with editing each cell in the array.

Here is the current for each code I am using - Thanks.

Dim cell As Range
    For Each cell In Sheets("sheet1").UsedRange
        cell.Value = cell.Value
Next cell

Upvotes: 0

Views: 2477

Answers (2)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

Something along these lines:

Dim aCells As Variant
Dim x As Long
Dim y As Long

aCells = Sheets("Sheet1").UsedRange

' Now do something with the array;
' We'll debug.print the contents of each element
'   to verify that it matches the cells in the sheet
For x = 1 To UBound(aCells, 1)
  For y = 1 To UBound(aCells, 2)
    Debug.Print aCells(x, y)
  Next
Next

Upvotes: 2

John Bustos
John Bustos

Reputation: 19574

Try this - MUCH faster and more efficient:

Sheets("sheet1").UsedRange.Value = Sheets("sheet1").UsedRange.Value

Upvotes: 3

Related Questions