comparing vertical list on one sheet to horizontal on another
Assuming your Columns to delete list is on Sheet1 in column A starting
at row 2 and the headings of the columns you want to delete are on
Sheet2 B1:M1 then try something like:
Sub DelCols()
Dim eRow As Long
Dim i As Long
Dim hdr As String
Dim header As Range
With Sheets("Sheet1")
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
hdr = .Cells(i, 1).Value
With Sheets("Sheet2").Range("B1:M1")
Set header = .Find(hdr)
End With
If Not header Is Nothing Then
header.EntireColumn.Delete
End If
Next i
End With
End Sub
Hope this helps
Rowan
Mike Mick wrote:
Hi there,
I'm currently working on a macro that takes a list of values from one sheet
as a 'Columns to delete' list. I want to take this list, select a set of
headings on another sheet, and if the heading matches my 'columns to delete'
list, delete the column.
I know how to select the 'columns to delete' range, and the headings to
check range on the other sheet. I'm also aware that as I'll be deleting
columns as I go I'll need to parse them in reverse, starting on the right and
going left. What I can't quite figure out is how to put all that together
into code.
In looking things up I believe I can do something like a 'For i =
varTitles.Columns.Count to 1 Step -1' to go in reverse, I'm just not sure how
to compare the contents of both lists. Any ideas? Much appreciated!!
-Mike
|