EXCEL VBA - Loop through cells in a row, if not empty, copy cell value into another row until next row not empty

Question

I'm considering writing some code to copy the data of row to the next until the next row is not empty. Suppose all in row 1 are headers.

Data is like:

    A2 ClientA       B2 Product1       C2 Price       D2 Quantity       E2 Cost
    A3               B3                C3 Price       D3 Quantity       E3 Cost
    A4               B4                C4 Price       D4 Quantity       E4 Cost
    A5 ClientB       B5 Product2       C5 Price       D5 Quantity       E5 Cost
    A6               B6                C6 Price       D6 Quantity       E6 Cost
    A7 ClientC       B7 Product3       C7 Price       D7 Quantity       E7 Cost
    A8               B8                C8 Price       D8 Quantity       E8 Cost
    A9               B9                C9 Price       D9 Quantity       E9 Cost

A3 and A4 will fill with ClientA and B3 and B4 will fill with Product until the next row is not empty.

I have searched to find a similar question, but I have no idea how to write it.

EXCEL VBA - Loop through cells in a column, if not empty, print cell value into another column

End results would be like:

    A2 ClientA       B2 Product1       C2 Price       D2 Quantity       E2 Cost
    A3 ClientA       B3 Product1       C3 Price       D3 Quantity       E3 Cost
    A4 ClientA       B4 Product1       C4 Price       D4 Quantity       E4 Cost
    A5 ClientB       B5 Product2       C5 Price       D5 Quantity       E5 Cost
    A6 ClientB       B6 Product2       C6 Price       D6 Quantity       E6 Cost
    A7 ClientC       B7 Product3       C7 Price       D7 Quantity       E7 Cost
    A8 ClientC       B8 Product3       C8 Price       D8 Quantity       E8 Cost
    A9 ClientC       B9 Product3       C9 Price       D9 Quantity       E9 Cost

Anyone have idea what the VBA code will be?

tjb1 · Answer 1 · 2016-07-08T15:24:08.797

2

If you wanted to use VBA, you can use this.

I wasn't sure how you wanted to find the last row with data so I provided a variable you can change yourself, it corresponds to the row. The first For loop using Z will loop through column A(1) and column B(2), inside this loop we have another For loop to go through the cells and enter the last seen value if the cell value is "".

Sub LoopFill()

Dim LastEntry As String, LastRow As Integer

LastRow = 25

For Z = 1 To 2
    LastEntry = ""
    For i = 1 To LastRow
        If Cells(i, Z).Value = "" Then Cells(i, Z).Value = LastEntry
        LastEntry = Cells(i, Z).Value
    Next i
Next Z

End Sub

edited Jul 08 '16 at 15:24

answered Jul 08 '16 at 14:56

tjb1

747
9
30

how about if i want to find last row on column E? add like: LastRow = activesheet.Cells(.Rows.Count, "E").End(xlUp).Row – Darwin Chan Jul 09 '16 at 04:05
1

If you found one of the answers to be helpful or answered your question, please mark it as the answer. – Adam Vincent Jul 31 '17 at 16:35

score 2 · Answer 2 · answered Jul 24 '17 at 12:36

Here's another way to do it without VBA.

Select Blanks (CTRL+G, Click Special, Select Blanks, OK)
In formula bar, type =(CELL DIRECTLY ABOVE THE FIRST BLANK) ex: IF A2 is blank, =A1
HOLD CTRL, press enter to commit formula to all blank cells.
Select range, copy and paste values if necessary to remove the formulas.
If you don't paste values, and try to do a sort or filter, the data will be wrong

score 1 · Answer 3 · answered Jul 08 '16 at 14:46

1

No VBA needed for this.

Write this formula in a helper column: =IF(ISBLANK(A2),F1,A2)

Assumes helper column is in column F. Drag this down the dataset, then copy the values into the original column. Repeat for column B.

answered Jul 08 '16 at 14:46

Scott Holtzman

27,099
5
37
72

i found this works even if some of C columns are empty, thx mate – Darwin Chan Jul 09 '16 at 03:48

score 1 · Answer 4 · answered Jul 08 '16 at 14:57

You'll need to:

SheetName: To the name of the sheet that you want processed
arColumns = Array("A", 2, 3): Add the Column numbers or letters that you want filled to this array

Sub FillRows()
    Const SheetName As String = "Sheet3"

    Dim lastRow As Long, x As Long, y
    Dim arColumns
    arColumns = Array("A", 2, 3)

    With Worksheets(SheetName)
        lastRow = .Rows(Rows.Count).End(xlUp).Row
        For x = 3 To lastRow
            For y = 0 To UBound(arColumns)
                If IsEmpty(.Cells(x, arColumns(y)).value) Then .Cells(x, arColumns(y)).value = .Cells(x - 1, arColumns(y)).value

            Next
        Next

    End With

End Sub

UBound(arColumns) is to find upper limit of the array, however the array declare is not dynamic i see? arColumns = Array("A", 2, 3) so how does the code work? — Darwin Chan, Jul 09 '16 at 04:22
`UBound(arColumns)` and `UBound(arColumns, 1)` return the last index of the first dimension of an array. — , Jul 09 '16 at 04:31

EXCEL VBA - Loop through cells in a row, if not empty, copy cell value into another row until next row not empty

4 Answers4