VBA to fill many ranges of empty cells in columns A and B with different cell values

[Armien7]

I hope everyone is having a wonderful new year so far. I'm really struggling with a macro I'm trying to build and I hope I can get some advice here

I have a sheet (called: "Motors") with 24720 rows. I'm trying to fill empty ranges with different intervals in column A and B with the values of the last filled cells of each range in column A & B. So for example: Range (A16, B16 and C16 : A27, B27 and C27) is empty, and I want to have it filled with the cell values of A15, B15 and C15, etc. Since I have 20.000 rows+, it takes a lot of time to do it manually.

The big problem I have is that each empty range differs from the previous one (one may containt more empty rows than the other one). For clarification I have made the following screenshots:

Hopefully I can get some tips and new insights

Best regards,
Armien

 

[Fluff]

How about

Sub Armien()
   With Sheets("Motors")
      With .Range("A1" & .Range("C" & Rows.Count).End(xlUp))
         .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
         .Value = .Value
      End With
   End With
End Sub

 

[Armien7]

How about

Sub Armien()
   With Sheets("Motors")
      With .Range("A1" & .Range("C" & Rows.Count).End(xlUp))
         .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
         .Value = .Value
      End With
   End With
End Sub

Hi Fluff! Thank you so much for your fast response and idea. Sadly I get the following error: With .Range("A1" & .Range("C" & Rows.Count).End(xlUp))

 

[Fluff]

Oops, replace the & with a comma.

 

[Armien7]

How about

Sub Armien()
   With Sheets("Motors")
      With .Range("A1" & .Range("C" & Rows.Count).End(xlUp))
         .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
         .Value = .Value
      End With
   End With
End Sub

[/QUOTE]

Oops, replace the & with a comma.

Once again many thanks Fluff!

The macro does the first empty range (AC15 : AC26) but unfortunately stops there. When I click on the run button again, I get the following error: .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"

 

[Fluff]

It stops at row 27 because that's the last row in the sheet. If you want that row copied down, how far down should it be copied?

 

[Armien7]

It stops at row 27 because that's the last row in the sheet. If you want that row copied down, how far down should it be copied?

The last row where it should be copied to is row: 24720.

 

[Fluff]

Ok, how about

Sub Armien()
   With Sheets("Motors").Range("A1:C24720")
      On Error Resume Next
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      On Error GoTo 0
      .Value = .Value
   End With
End Sub

 

[Armien7]

Thank you so much for your inputs Fluff! This time I never get an error when running the macro (even when spam cliking my macro button). But just as before it stops at row 27 (which is correct), but then it does't copy row 28 and past it to the next empty range. I hope I'm clear with my explanation, one again many thanks for helping me

 

[Fluff]

Ok, how about

Sub Armien()
   With Sheets("Motors").Range("A1:C24720")
      .Offset(24719).Resize(1, 1).Value = 1
      .Offset(24719).Resize(1, 1).Value = ""
      On Error Resume Next
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      On Error GoTo 0
      .Value = .Value
   End With
End Sub