Microsoft Office Forums - View Single Post - [Solved] Excel VBA Macro

excelledsoftware · 12-09-2014, 10:36 PM

My guess is that you are in the process of learning VBA and you have several headers that you want to apply this too. You want to see what the code looks like so you can edit for other procedures. Regardless if I am right or not below is a code that will do just that for you.

Code:

Sub DeleteDataBelowHeader()
  'Code searches out headers and then deletes all data below that header.
  Dim LastCol As Long, SearchString As Variant, HoldString As String
  Dim CheckCol As Long, v As Variant
  
  LastCol = Range("IV1").End(xlToLeft).Column 'Set the Final Column
  
  SearchString = Array("testing", "testing2") 'Add more strings separated with commas
  
  For Each v In SearchString 'Check all in the array
  'Search each column for the header
    For CheckCol = 1 To LastCol
      If Cells(1, CheckCol).Value = v Then
        HoldString = Cells(1, CheckCol).Value
        Columns(CheckCol).ClearContents
        Cells(1, CheckCol).Value = HoldString
      End If
    Next CheckCol
  Next v
End Sub

This code uses Macropod's suggestion as well.

As with all code be sure to save your workbook before running because VBA cannot undo the operation after it is done. This code is assuming that your headers are on row 1.

Let me know if you have any questions.