Automatic column deletion

[David Mulvey]

In a nutshell, is there any way to have a column delete itself
automatically based on established parameters...specifics follow.

I am running Excel 2002 SP2

I receive an excel spreadsheet feed from an external source. The feed
consists of everything shipped out of a distribution warehouse to all
locations it ships to in the world. The material is supposed to be
segregated by destination. That is; the contents of each container is
supposed to be destined for only one location. The contents and its
destination are burned onto a Radio Frequency (RF) tag, which is used
for tracking the shipment.

I am only concerned with 60 of the 200 plus locations that the
distribution center ships to. It is easier and quicker for the data
provider to feed me everything than to cull the 60 locations I need.
Through a pivot table, I have been able to list the RF tag number in
column A, and all the destinations in row '5' (the row number is not
relevant). Above the destinations in row '4', I have utilized a
"CountIf" function matching each destination cell in row '5' with a
table of valid destinations located on a separate worksheet. If the
destination in row 5 is valid, the number '1' appears in the row '4'
cell directly above the location. If the destination is not in the
table, I get a '0' directly above the location.

I would like to remove all the columns with a '0' above the location
in row '4', or at least be able to delete all the information in the
'0' rows.

The table looks something like this.

A B C D E ....
1valid LOC 0 1 1 0


2Location 123 3e4 4t2 999

3(Tag) 100 1 0 0 0

4(Tag) 101 0 1 0 0

5(Tag) 103 0 1 1 0

6(Tag) 104 1 1 0 0
..
..
..

(The word "Tag" is in () for post purposes only. The tags are all
identified numerically.)

Here's what I am looking for:

I don't care about tag 100, as there is a '0' cell 'B1'. I would like
that column (or its data) deleted.

Tag 101 is a valid pure pack as its cargo is all going to location
3e4.

Tag 103 is valid but is not a pure pack as its cargo is destined for
locations 3e4, and 4t2.

Tag 104 is an anomaly that should never happen as it has cargo going
to an invalid location as well as a valid location. The odds on this
happening are so small I am not concerned that the invalid tag now
becomes valid due to the deletion of column B. There is so much data
being thrown at me, that one anomaly is not going to skew the data at
all.

This is my first post and I apologize for its length. I hope the
information is clear.

Any and all information and input is appreciated.

Thanks in advance.

 

[Nikos Yannacopoulos]

David,

This is very easy to achieve with a few lines of code:

Sub remove_redundant_columns()

Range("A1").Select
Selection.SpecialCells(xlLastCell).Select
lr = ActiveCell.Row
CheckRow = 4
ActiveCell.Offset(CheckRow - lr, 0).Select
Do
If ActiveCell.Value = 0 Then
Columns(ActiveCell.Column).Delete shift:=xlToLeft
End If
If ActiveCell.Column = 1 Then Exit Do
ActiveCell.Offset(0, -1).Select
Loop

End Sub

HTH,
Nikos