Fill in blank Excel cells using value from another column value but not in the same row

Question

I have blank cells in column G and H (the blank cells will always be in the same row as each other) that i would like to fill with cell values from non-contiguous columns, K and L (data always starts in K13 and L13).

For the columns, i know that the range would start on row 13, but the last row of data varies. For columns K and L, column K data needs to go to column G and column L needs to go to column H.

If for example, i had data in k13:l14, and an empty row of data in G20:H20 and G24:H24, is there a way i can copy the data in k13:L14 over to the empty cells WITHOUT KNOWING where the empty row of data starts?

Answer 1

Try this:

Sub fillGaps()
    Dim wrkSheet As Worksheet
    Set wrkSheet = ActiveSheet 'Change to Sheets("[name]")
    
    Dim fillerRange As Range
    Set fillerRange = wrkSheet.Range("L13:M13")
    
    Dim gapRange As Range
    Set gapRange = wrkSheet.Range("G13:H13")

    While hasData(fillerRange)
        While hasData(gapRange)
            Set gapRange = gapRange.Offset(1, 0)
        Wend
        Set fillerRange = fillerRange.Offset(1, 0)
        fillerRange.Offset(-1, 0).Copy gapRange
        fillerRange.Offset(-1, 0).Clear
    Wend
    
End Sub

Function hasData(r) As Boolean
    For Each cell In r
        If cell.Value <> "" Then
            hasData = True
            Exit Function
        End If
    Next cell
    hasData = False
End Function

Note: This only works if the active sheet is the one with the gapped data. See my comment on how to fix this.