Autofill until next row not blank

Question

I have a preformatted spreadsheet report with cells formatted like below:

A           B         C        D
                               Week1 Week2
CUSTOMER A  PRODUCT A MEASURE A
                      MEASURE B
                      MEASURE C
CUSTOMER B  PRODUCT B MEASURE A
                      MEASURE B
                      MEASURE C

Report can contain thousands of rows. I need to unmerge cells in column A and B, which results in 3 cell blocks, with string contained in the first row of the block. A2 in this case.

I need to autofill the cells in Columns A and B to achieve the following situation:

CUSTOMER A PRODUCT A MEASURE A
CUSTOMER A PRODUCT A MEASURE B
CUSTOMER A PRODUCT A MEASURE C
CUSTOMER B PRODUCT B MEASURE A
CUSTOMER B PRODUCT B MEASURE B
CUSTOMER B PRODUCT B MEASURE C

Values should be autofilled 2 rows or until cell that contains a value not equal to autofilled value. Then that value should be autofilled until next 2 rows, switched to the value contained in the third cell et cetera.

It should continue to do so until last row-1 containing values (last row contains date and time).

I would like to do this by a VBA loop. Columns A, B and C are always strings and values from Column D onwards are numbers.

Answer 1

Hopefully this is what you need. I've added comments above each significant line so you can track what's going on, and make adjustments as necessary.

This does assume that your data in column C is representative of the rows you need to work on. You said that it should continue until last row - 1 as the last row contains date+time... well, so long as it's not column C that contains the date+time, then this'll be fine:

Dim myData As Range

'Set a range by figuring out the length of your data based on column C data:
Set myData = Range("C2", Range("C2").End(xlDown))

'create two blank variables that we'll use
'to store data later:
colAdata = ""
colBdata = ""

'Loop through each row in the 'myData' range:
For Each cell In myData

    'First, unmerge cells that are merged in columns A&B:
    If cell(1, -1).MergeCells = True Then cell(1, -1).UnMerge
    If cell(1, 0).MergeCells = True Then cell(1, 0).UnMerge

    'Check if column A has data, and if so, grab its value and
    'column B's value for use in the next iteration:
    If cell(1, -1).Value <> "" Then
        colAdata = cell(1, -1).Value
        colBdata = cell(1, 0).Value
    Else 'it's blank so enter the data from the row above:
        cell(1, -1).Value = colAdata
        cell(1, 0).Value = colBdata
    End If

Next

Hope this helps, let me know how it goes.