Copy down to next row where there is data

[gheyman]

I have data running down column A (Part Numbers)
Example: I have a Part number in A2 but don't have another Part number until A45 and then another lets say in A55

How can I copy A2 down to A44 and then A45 down to A54 and then A55 down to last row
(I can determine LastRow by using Column B)

My goal is not to have blanks in column A

The above is just an example. the second part number could be anywhere in column A its not always going to be in "45" and the next will not always be in "55"... But the first will always be in A2

Thanks for your time and help!

 

[DanteAmor]

Try this

Sub Macro4()
  With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[gheyman]

wont that just copy A2 all the way down? I need to copy A2 down to where the next Part number is listed in column A (in the example above it would copy down to A44, then I would the code to copy A45 down to A54 and so on)

 

[DanteAmor]

Did you try the macro?

 

[gheyman]

No, I tried to read the code. I guess I didn't understand it.

My apologies, I will try it now.

 

[gheyman]

Perfect!

Again, my apologies for not trying first.

I appreciate the help very much.

 

[DanteAmor]

No, I tried to read the code. I guess I didn't understand it.

My apologies, I will try it now.

What the code does is, say, select the empty cells in the range A2 to the last cell according to column B.
Put a formula to take the data from the previous cell.
Finally, change the formula to value.

 

[DanteAmor]

Perfect!

Again, my apologies for not trying first.

I appreciate the help very much.

Hi @gheyman, I'm glad to help you. Thanks for the feedback.

 

[MARK858]

Just another option as I was passing through....

Sub FillCell1()
    Dim fCell As Range
    On Error Resume Next

    For Each fCell In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(4).Areas
        fCell.Value = fCell(1).Offset(-1).Value
    Next

    On Error GoTo 0
End Sub