With then For Next or For Next then With

[tiredofit]

Which is faster?

With Sheet1
    
    For counter = 1 To 100000
        
        .Cells(counter, 1).Value = counter
 
    Next counter
      
End With

or

For counter = 1 To 100000

    With Sheet1
        
        .Cells(counter, 1).Value = counter
     
    End With

Next counter

I'm inclined to think the former is faster but tested it and saw no difference.

Thanks

 

[BarryL]

both very similar, I timed them and came out about the same. something like the below would be 10000000000000000000000000000000% quicker though

sub test()
Range("A1:A100000").Value = Evaluate("=ROW(A1:A100000)")
end sub

 

[tiredofit]

Thanks, never seen your method before.

I just thought the fewer things you do inside a loop, the quicker it'll be.

I normally do this:

Dim a As Long

a = 100000

ReDim MyArray(1 To a, 1 To 1) As Variant

Dim counter As Long

For counter = 1 To a

    MyArray(counter, 1) = counter

Next counter

Sheet1.Cells(1, 1).Resize(a, 1).Value = MyArray

 

[BarryL]

definitely. obviously the above is a bad example as theyre the same amount of lines. the more things in a loop the worse it is. And if evaluate statements are available id use them

 

[tiredofit]

Agree.

BTW, why does this not work?

Dim a As Long
a = 1000
Range("A1:A" & a).Value = Evaluate("=ROW(A1:A)" & a)

returns #VALUE instead of 1,2,3, etc.

 

[Norie]

=ROW(A1:A)1000 is not a valid formula, and that's the string you are trying to evaluate.

What is it you are trying to do?

 

[tiredofit]

Just trying the suggestion by BarryL but using a variable instead of a fixed row number.

 

[Norie]

Try this then.

Dim a As Long
a = 1000
Range("A1:A" & a).Value = Evaluate("=ROW(A1:A" & a & ")")

 

[tiredofit]

Thanks, that did the trick!