Macro to copy cells to a variable range and then loop

[Cam Johnston]

Needing a macro that can copy cells on a line in columns B, C and D to a variable range in those same columns defined by the next line of the spreadsheet that has text in columns B, C and D and then repeat. I have tried a number of loops but failed miserably. I have attached images of what the input looks like "before" and desired "after."

Very grateful for any pointers or assistance that can be provided.

Thank you in advance.

BEFORE

AFTER

 

[Bosquedeguate]

Please provide your attempts at VBA code. It may just need some minor tweaking.

 

[Cam Johnston]

Thanks for the quick follow-up. I "attempted" to create a macro capturing the relative references. and then place it in a loop.

Clearly, the relative references is not working as intended

Here is the VBA looking forward the constructive feedback

Sub UnknownNumRows()

Range("a15").Select

Do Until IsEmpty(ActiveCell)

Range("B19").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlToLeft)).Select

Range("B19:D19").Select

Selection.Copy

Range(Selection, Selection.End(xlDown)).Select

Range("B19:D33").Select

ActiveSheet.Paste

Selection.End(xlDown).Select

Range("A39").Select

Loop

End Sub

 

[kevin9999]

There's a number of different ways you could do this. Here's just one suggestion:

Option Explicit
Sub CamJohnston()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Dim LRow As Long, r As Range, c As Range
    LRow = ws.Cells(Rows.Count, "E").End(xlUp).Row
    Set r = ws.Range("B2:D" & LRow)
    
    For Each c In r
      If c = "" Then
        c.FillDown
      End If
    Next c
End Sub

 

[Cam Johnston]

Your suggestion was brilliant! Thank you

 

[kevin9999]

Your suggestion was brilliant! Thank you

Happy to help Cam and thanks for the feedback