For loop is taking very long time to run-CodePudding

In Macro, I am using the below code to autofill the columns. The Excel sheet has nearly 90k rows it's working but it's taking more than an hour to complete. Is there any other way, other than For Loop.

Can someone guide me in this?

For reference I have attached a sample snapshot in the link for autofilling method.

[https://i.stack.imgur.com/k4Wu9.png][1]

irow10 = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
     
For i = 1 To irow10
If ActiveSheet.Cells(i, 4) = "" Then
ActiveSheet.Cells(i, 4).Value = ActiveSheet.Cells(i - 1, 4)
End If

Next

CodePudding user response：

As I alread wrote: try powerquery first.

If you want to keep with VBA than you should use an array to do the "transformation" and then write back to the sheet

Sub fillDown()

Dim lastRow As Long
lastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Dim rgData As Range
Set rgData = ActiveSheet.Cells(1, 4).Resize(lastRow)

Dim arrData As Variant
arrData = rgData.Value

Dim i As Long
For i = 2 To lastRow
    If arrData(i, 1) = vbNullString Then
        arrData(i, 1) = arrData(i - 1, 1)
    End If
Next

rgData.Value = arrData

End Sub

CodePudding user response：

Why not use a simple Excel formula for this?

I have just created an Excel sheet, containing the following data:

Col1 Col2
A       1
B       2
C       <BLANK>
A       1
B       2
C       <BLANK>
A       1
B       2
C       <BLANK>
...
(up to 97345 cells (you mentioned 90k))

Then, I selected all cells in the next column and wrote the following formula:

=IF(ISBLANK(B2),A2,B2)

After that, I pressed Ctrl ENTER for entering that formula in all selected cells.

I got following results:

Col1 Col2        Col3
A       1           1
B       2           2
C       <BLANK>  C
A       1           1
B       2           2
C       <BLANK>  C
A       1           1
B       2           2
C       <BLANK>  C
...

This was done in a matter of seconds!

I had also recorded my actions, and I ended up with such a macro (after some editing):

Sub Macro1()
    Range("C2:C97345").FormulaR1C1 = "=IF(ISBLANK(RC[-1]),RC[-2],RC[-1])"
End Sub

Executing this macro also does not even take a second.