title: VBA Move data to the left (4 columns need to be moved per row, may have blank within those 4 columns that need to be kept)
body: <p>I have a a dataset that has a set of questions. However part of the data has been duplicated (4 columns) for the respondents. These need to be merged into just 4 columns based on heading they have (answer 1, answer 2, answer 3, answer4). </p>
  
  <p><a href="https://i.stack.imgur.com/SB8En.jpg" rel="nofollow noreferrer">Heres the example image</a></p>
  
  <p>I've tried this:</p>
  
  <pre><code>Sub MoveLeft()
  Dim r As Long, rws As Long
  
  Application.ScreenUpdating = False
      With ActiveSheet.UsedRange
          rws = .Rows.Count
          r = 1
          On Error Resume Next
          Do
              .Rows(r).Resize(8000).SpecialCells(xlBlanks).Delete Shift:=xlToLeft
              r = r + 8000
          Loop While r &lt;= rws
          On Error GoTo 0
      End With
      Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>But It didn't keep the blanks I needed</p>