Hi,
After several attemps at writing a Macro I need help! I am working in Excel 2007.
I have several Columns some of which are labeled "keep" and some are labelled "delete"
Is there a Macro that could delete all the data in Columns where the Column title is "delete" and keep the rest of the data?
Alternatively the macro could copy only the columns that are labelled "keep" onto a new sheet in the same workbook.
Any help writing a Macro would be great.
Thank you
Configuration: Windows XP Internet Explorer 7.0 Excel 2007.
Suppose that column headings (labels) are in row no 1 frrm A1 to right
Sub test()
Dim del() As Range, rng As Range, j As Integer, k As Integer
Dim add As String
On Error Resume Next
Worksheets("sheet1").Activate
Set rng = Range(Range("A1"), Range("A1").End(xlToRight))
j = WorksheetFunction.CountIf(rng, "delete")
ReDim del(1 To j)
k = 1
Set del(k) = rng.Cells.Find(what:="delete", lookat:=xlWhole)
If del(k) Is Nothing Then Exit Sub
add = del(1).Address
MsgBox del(k).Address
k = k + 1
Do
Set del(k) = rng.Cells.FindNext(after:=del(k - 1))
If del(k) Is Nothing Then GoTo line1
If del(k).Address = add Then GoTo line1
MsgBox del(k).Address
k = k + 1
Loop
line1:
k = 0
For k = j To 1 Step -1
del(k).EntireColumn.Delete
Next k
End Sub
mine is exscel 2002 version |