VBA Find Blank cells in Column Headings

Question

I hope you can help. I have a piece of code below, and its working somewhat. I just need it to do more.

It currently looks along the first row from A1 to H1. If it finds a blank cell then it copies the cell value to the left of the blank cell, then pastes this value into the blank cell and then moves along.

As the range can change from day to day A1 to H1 will not suffice. I now need the code to look along the first row, until it finds the last cell with data in it then look for the blanks and start the copy and paste process.

I also need the code to then add a 2 to the pasted cell so that I can perform a pivot and differentiate between the copied cells and the pasted.

I have provided a picture below for better understanding. The end result should be that cell B2 contains the text 24 - Company: Hier 2 and E2 contains the text 07 - Product: Family Hier 2

My code is below and as always any and all help is greatly appreciated.

Pic 1

MY CODE

Public Sub BorderForNonEmpty()
    Dim myRange As Range
    Set myRange = Sheet1.Range("A1:H1")
    For Each MyCell In myRange
        If MyCell.Text = "" Then
            MyCell.Offset(0, -1).Select
        ActiveCell.Copy
        ActiveCell.Offset(0, 1).PasteSpecial (xlPasteAll)
        End If
    Next
End Sub

Answer 1

Try the code below - the comments indicate what each important line is doing:

Option Explicit

Sub FillInHeaders()

    Dim ws As Worksheet
    Dim lngRowWithHeaders As Long
    Dim rngHeader As Range
    Dim rngCell As Range

    ' get a reference to your worksheet
    Set ws = ThisWorkbook.Worksheets("SHeet1")

    ' set the row that the headers are on
    lngRowWithHeaders = 2

    ' get the range from A1 to ??1 where ?? is last column
    Set rngHeader = ws.Cells(lngRowWithHeaders, 1) _
        .Resize(1, ws.Cells(lngRowWithHeaders, ws.Columns.Count) _
        .End(xlToLeft).Column)

    ' iterate the range and look for blanks
    For Each rngCell In rngHeader
        ' if blank then ...
        If IsEmpty(rngCell.Value) Then
            ' get cell value from left and a 2
            rngCell.Value = rngCell.Offset(0, -1).Value & "2"
        End If
    Next rngCell

End Sub