title: VBA Find Blank cells in Column Headings
body: <p>I hope you can help. I have a piece of code below, and its working somewhat. I just need it to do more. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>I have provided a picture below for better understanding. The end result should be that cell B2 contains the text <strong><em>24 - Company: Hier 2</em></strong> and E2 contains the text <strong><em>07 - Product: Family Hier 2</em></strong></p>
  
  <p>My code is below and as always any and all help is greatly appreciated. </p>
  
  <p>Pic 1</p>
  
  <p><a href="https://i.stack.imgur.com/tzSda.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tzSda.png" alt="enter image description here"></a></p>
  
  <p>MY CODE </p>
  
  <pre><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
  </code></pre>