Excel VBA Macro

MD011 · #1 12-08-2014, 08:43 AM

Good afternoon everybody,

I am looking for some of your kind assistance to assist me in creating a macro for excel that will do the following:

I need the macro to search the workbook for specific headers in columns, for example "testing", and then delete all data in the cells below that header in that column, making sure to leave the header in place.

So for example if the column name was "Testing" then the macro would match "Testing" then delete all the data in that column BELOW the header.

I hope that makes sense. I am relatively useless to VBA etc as I am very new to it.

Any help would be greatly appreciated.

Thank you.

macropod · #2 12-09-2014, 09:31 PM

Why not simply delete the entire column's contents, then reinsert the header label?

excelledsoftware · #3 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.

MD011 · #4 12-10-2014, 02:15 AM

Thank you very much for the help it is much appreciated.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
need macro insert row under specific data	gogita_79	Excel Programming	2	09-15-2014 01:48 AM
Vba macro code for grouping the data based on 2 hours time	dharani suresh	Excel Programming	5	04-29-2014 03:25 AM
Deleting rows with specific criteria	joflow21	Excel	9	11-22-2013 12:10 PM
MACRO - Insert row based on Form Field Criteria	Elan05	Word VBA	5	04-16-2013 06:39 AM
Change values in cells based on criteria	SaneMan	Excel Programming	2	02-02-2012 07:58 AM