asked on

excel 2010 unmerge cells the populate blank cells with data from above

Could an expert help me out with some VBA code on this one please.

I need to unmerge all cells then starting in first blank cell afetr cell 'A6' copy the data from the cell above.

Do the same from B7, C7, D7 and E7

so....if A6 has abc in it copy abc into A7 A8 etc until next non blank cell if A20 has xyz in it copy xyz into A21...... until next non blank etc etc.

Do the same from B7, C7, D7 and E7

Thanks in advance

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

brilliant thanks

Jacques Geday

Hi is this what you want ?? Paste this code in the sheet that you need to convert or in a module.

Sub PasteAfter()
Dim WS As Worksheet
Dim ExcelLastCell As Object
Dim MaxRow As Long, I As Long, J As Long
Dim CelltoCopy

Set WS = ActiveSheet
WS.DisplayPageBreaks = False
Set ExcelLastCell = WS.Cells.SpecialCells(xlLastCell)
MaxRow = ExcelLastCell.Row
MaxCol = ExcelLastCell.Column
WS.UsedRange.UnMerge
CelltoCopy = WS.Range("A6")

For J = 1 To MaxCol
    For I = 6 To MaxRow
        If WS.Cells(I, J) <> "" Then
            CelltoCopy = WS.Cells(I, J)
        Else
            WS.Cells(I, J) = CelltoCopy
        End If
    Next I
    CelltoCopy = ""
Next J
End Sub

Open in new window

I copied in this workbook Sheet1 (original) as a backup. Press on the button Paste After in Sheet1 and check the results.

I started at row 6 as you explicitly said: After cell A6 don't know if this was an example and you want to do the whole sheet. If it is the case then replace in the routine this line
For I = 6 To MaxRow

by this Line
For I = 1 To MaxRow

gowflow
PasteAfter.xlsm

Jacques Geday

Ooops I guess I was late !!!

Just a note in Rory solution if you happen to have no items in col A then the routine fails.
gowflow