Macro Help for Finding and Deleting Specific Columns 1

[gall3on]

I'm not sure if I should have placed this in the VB forum but I need some assistance with a macro I'm trying to create.

I want to be able to delete a column based on the first row heading. How can this be achieved in a Macro?

For example:
Version Pick Test Verification
1 x Test B
2 d Cntrl C
3 f Test X

I need the Macro to find Col A and Col C and then delete them. The table that needs to get updated is sometimes out of order in columns so I can't just select the same columns. The headings stay the same though. I actually have to find them first and then delete.

Any assistance would be appreciated.

 

[xlbo]

with column headers in row 1 and starting in colA:

Dim lCol As Integer, i As Integer

Const Header1 = "1st Column Header"
Const Header2 = "2nd Column Header"

lCol = Range("A1").End(xlToRight).Column

For i = 1 To lCol
   If Cells(1, i).Text = Header1 Or Cells(1, i).Text = Header2 Then
       Columns(i).Delete
   End If
Next i

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Use the Find method and then, having found, the EntireColumn property to delete...

dim r as range. sFindText as string
set r = sheet1.rows(1).find(sFindText)
if not r is nothing then r.entirecolumn.delete shift:=xlleft

Of course, you'll have to assign a value to sFindText.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Bong]

for each c in sheet1.usedrange.columns
   if c.rows(1).value="version" then c.delete
next

and so on

_________________
Bob Rashkin

 

[lmbayley]

I like Skip's method here, although I don't think I would have thought of it myself, because I still tend to think in terms of iteration, forgetting some of the handy methods built into the language.

For xlbo's method, would it be better to go from right to left, i.e.

For i = lCol to 1 Step -1

If you work from left to right and delete a column, the columns to the right of the deleted column all shift to the left. That means that if you delete column 2, column 3 now becomes column 2, but in the next iteration, i=3, so you skip a whole column.

I'm not completely sure of the "For each" structure that Bong suggests. Would this also process the columns from left to right?

-LB

 

[SkipVought]

When DELETING Rows or Columns using For...Next, you ought to proceed from HIGHEST to LOWEST. Otherwise, when you DELETE, the current reference is LOST; so what is NEXT?

iteration

dim sHead(1), i
dim r as range. sFindText as string
Head(0) = "head1"
Head(1) = "head2"
for i = 0 to 1
  set r = sheet1.rows(1).find(Head(i))
  if not r is nothing then r.entirecolumn.delete shift:=xlleft
next

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[xlbo]

yup - good catch - should take my own advice sometimes !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gall3on]

I used a variation of Skip's script:

dim sHead(1), i
dim r as range. sFindText as string
Head(0) = "head1"
Head(1) = "head2"
for i = 0 to 1
set r = sheet1.rows(1).find(Head(i))
if not r is nothing then r.entirecolumn.delete shift:=xlleft
next

But realized a flaw that needs to be addressed. My 'raw' table has two columns labeled 'ID' and 'CID'. My script is supposed to remove the 'ID' column but the 'CID' column gets removed instead.

This is a portion of the script:
..

Dim sHead(68), i
Dim r As Range

sHead(0) = "ID"
sHead(1) = "Type"

For i = 0 To 68
Set r = ActiveSheet.Rows(1).Find(sHead(i))
If Not r Is Nothing Then r.EntireColumn.Delete Shift:=xlLeft
Next

..

How can I force it to find just 'ID' and not delete the Column labeled 'CID'?

 

[SkipVought]

once you FIND "ID", first test the VALUE...

  Set r = ActiveSheet.Rows(1).Find(sHead(i))
  If Not r Is Nothing [b]AND Len(f.value) = 2[/b] Then r.EntireColumn.Delete Shift:=xlLeft

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]