Filling in blanks using VBA

[dembele2504]

Hi,

I have a (hopefully) fairly simple problem for some of you guys.

In my data, the A column is a product code. B through to L is other data related to that product. If the product code appears more than once (which is ok, because the data, such as location, may be different) then there are just empty cells below. I have used VBA to open this from a txt source in excel.

The below is directly copied from my excel sheet. The gaps are where the same product code should appear as above. So for example: "8190268" should appear a further three times below the top one. What is the cleanest way to achieve this please? Please bear in mind the sheet may contain a lot more rows than in this example and also will change on a daily basis when importing the updated version.

To clarify, I don't want any blanks in the A column, and any current blanks have beside them data relating to the product above. So I just want that product code to be in the blank cells.

Thanks in advance.

Product
8083958
8084248
8084518
8182038
8186058
8186078
8190268



8220048
8225848
8244288
8244398
8260708
8340948
6694948
7935598
7935608
7935618
7935648

7639038

[Greg M]

Hi there,

See if the following code does what you need:

Option Explicit


Sub FillInBlankCells()

    Const sPRODUCT_COLUMN   As String = "A"
    Const iFIRST_DATA_ROW   As Integer = 2
    Const sSHEET_NAME       As String = "Product Data"

    Dim rFirstCell          As Range
    Dim rLastCell           As Range
    Dim rLastRow            As Range
    Dim rCell               As Range
    Dim wks                 As Worksheet

    Set wks = ThisWorkbook.Worksheets(sSHEET_NAME)

    With wks

        Set rFirstCell = .Range(sPRODUCT_COLUMN & iFIRST_DATA_ROW)

        Set rLastRow = .UsedRange.Rows(.UsedRange.Rows.Count)

    End With

    Set rLastCell = Intersect(rLastRow, _
                              rFirstCell.EntireColumn)

    For Each rCell In Range(rFirstCell, rLastCell).Cells

        If rCell.Value = vbNullString Then
            rCell.Value = rCell.Offset(-1, 0).Value
        End If

    Next rCell

End Sub

The highlighted values may be changed to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[dembele2504]

Hi Greg,

Thank you so much for your time, this worked an absolute treat. Exactly what I needed.

[alansidman]

If you load your worksheet into Power Query you can use the Fill function to fill down the empty cells with the data above. It is a one step feature in Power Query.

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Product"})
in
    #"Filled Down"

See in my signature how to apply

[Greg M]

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to help.

Regards,

Greg M

[Fluff13]

Another option

Sub dembele2504()
   With Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub