comparing vertical list on one sheet to horizontal on another

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

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

Rowan,

First off, thanks for the quick response. I believe I understand your code
except for the first part in setting eRow and then looping 2 to eRow. Can
you explain what these lines are doing:

With Sheets("Sheet1")
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 to eRow
....

I think I get the rest... Thanks!
-Mike

"Rowan Drummond" wrote:

> 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
>

Mike

eRow = .Cells(Rows.Count, 1).End(xlUp).Row
Sets the variable eRow by starting at the last cell in column A
(cells(rows.count,1) and then moving up until it finds a non empty cell.
Therefore eRow is set to the rownumber of the last used cell in Column
A. Another way of describing it is that eRow is set to the rownumber of
the cell that would be selected if you clicked in cell A65536 and then
hit Ctrl+UpArrow

For i = 2 to eRow
then loops through each row in column A starting in row 2 as I have
assumed headings in row 1.

Hope this makes sense
Rowan

Mike Mick wrote:
> Rowan,
>
> First off, thanks for the quick response. I believe I understand your code
> except for the first part in setting eRow and then looping 2 to eRow. Can
> you explain what these lines are doing:
>
> With Sheets("Sheet1")
> eRow = .Cells(Rows.Count, 1).End(xlUp).Row
> For i = 2 to eRow
> ...
>
> I think I get the rest... Thanks!
> -Mike
>
> "Rowan Drummond" wrote:
>
>
>>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
>>

"Rowan Drummond" wrote:

> Mike
>
> eRow = .Cells(Rows.Count, 1).End(xlUp).Row
> Sets the variable eRow by starting at the last cell in column A
> (cells(rows.count,1) and then moving up until it finds a non empty cell.
> Therefore eRow is set to the rownumber of the last used cell in Column
> A. Another way of describing it is that eRow is set to the rownumber of
> the cell that would be selected if you clicked in cell A65536 and then
> hit Ctrl+UpArrow
>
> For i = 2 to eRow
> then loops through each row in column A starting in row 2 as I have
> assumed headings in row 1.
>
> Hope this makes sense
> Rowan
>
> Mike Mick wrote:
> > Rowan,
> >
> > First off, thanks for the quick response. I believe I understand your code
> > except for the first part in setting eRow and then looping 2 to eRow. Can
> > you explain what these lines are doing:
> >
> > With Sheets("Sheet1")
> > eRow = .Cells(Rows.Count, 1).End(xlUp).Row
> > For i = 2 to eRow
> > ...
> >
> > I think I get the rest... Thanks!
> > -Mike
> >
> > "Rowan Drummond" wrote:
> >
> >
> >>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
> >>
>