Delete Columns with duplicate headers

[Casey]

Hi,
I have a range of column headers ("K1:IP1") that I would like to loop
through and find all duplicates. Then delete the entire column with the
duplicate header. I would like to keep the original header and column.
Example:
K L M N O
P..........................
Stone CMU Brick Grout Stone Stone

Columns O & P would be deleted but column K would remain.

 

[Ardus Petus]

Hi Casey

Sub DelDupeCols()
Dim lCol As Long
Dim rFound As Range
For lCol = Columns("P").Column To Columns("L").Column Step -1
Set rFound = Range(Range("K1"), Cells(1, lCol - 1)).Find( _
what:=Cells(1, lCol).Value, _
LookIn:=xlValues, _
lookat:=xlWhole)
If Not rFound Is Nothing Then
Columns(lCol).Delete
End If
Next lCol
End Sub

HTH

 

[Casey]

Ardus,
Perfect! I will need to study what makes this routine work. I am
particularly befuddled by the Columns("P").Column To Columns("L")
statement. Usually when I see a bit of code I can see the logic right
away, but this has me scratching my head. Thank you very much for the
reply. Good stuff.

 

[Guest]

For lCol = Columns("P").Column To Columns("L").Column Step -1

is the same as

for lCol = 16 to 12 step -1

confirming from the immediate window:

? columns("P").column
16
? columns("L").column
12

 

[Casey]

Tom,
Thanks for the explaination. I think what threw me was the Column
("P") since my OP set the range to column IP. After I studied it
while I connected the dots. But I always appreciate the lessons fro
you.
I tried to go bankrupt for 3 years teaching high school and I just ha
the light go on for using the immediate window. Good teachers do tha
for people. I have very little knowledge about how to use i
effectively. I'm getting pretty good with watches, but I would lov
some training in using the Immediate window. Right now it is hit o
miss, whether I get any useful insight from the Immediate window.
keep trying. Thanks again

 

[Tom Ogilvy]

Basically, you can execute almost any individual command/line of code that
you can write in a module from the immediate window

That is one way to use it. This way you can test what the code is going to
do or use it to perform something on your sheet that is easier to code than
to do manually.

The other way to use it is to use debug.print statements in your actual
code to monitor what is going on. Then your code flows without stopping or
without altering the environment in which it is running. (many times you
see people say they can step through their code, but it doesn't work when
they run it).

When your code errors and you go to debug, you can test variable values in
the immediate window (or I assume you can use the watch window - I can't
say, because I never use it - so I am not the best source for information on
the watch window).

 

[Casey]

Tom,
Thanks for the immediate window lesson and on a Sunday no less.