0

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.

  • Comment on question formatting, is it not advisable to indicate Excel version in the question, as features are different between versions? – SpaceRock Jun 25 '14 at 12:13
0

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.

  • Thanks. This sub works pretty fine. Since the report is heavily preformatted in terms of colours and borders, I would need help with the following: Make autofill preserve the formatting of the source cell where it grabs the value to be filled. Otherwise great loop. – SpaceRock Jun 25 '14 at 13:21
  • Great, glad it worked. Re the formatting, let me make sure I understand. The merged cells (for example cell A2) carry colour and border formatting, and we need to preserve that formatting so that it's there in A2 only after unmerging? Or we need to preserve in A2 AND also copy that formatting to the subsequent rows, A3, A4? – David Jun 25 '14 at 20:07
  • The latter should take place, formatting has to be preserved for subsequent rows. – SpaceRock Jul 25 '14 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.