Automatic column deletion

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.
0
12/3/2003 3:55:29 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
749 Views

Similar Articles

[PageSpeed] 47

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
>-----Original Message-----
>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.
>.
>
0
12/4/2003 12:54:53 PM
Reply:

Similar Artilces:

Totaling rows & columns in the same cell
I need to ensure the total in specific rows add up to the total of column - so that the total for both, if equal, appear in the same cell For example I need the total in cell G19: to add certain rows such as =Sum(C19,E19,F19), if equal to the sum of =sum(G1:G18). If not equal I need to receive an error message. How do I do this -- Message posted from http://www.ExcelForum.com You could try =IF(SUM(C19,E19,F19)=SUM(G1:G18),"True","Error") You can replace the "True" with SUM(G1:G18) if you want the total t apper instead. =IF(SUM(C19,E19,F19)=SUM(G1:G18),SUM(...

automatically adjust sale price
When I add a product it does ask the purchase price and the sale price but I am looking for a easier way so that all he products have the markup added to it instead of manually doing it and risking a error. How do I do this? Money 2004 SBE ...

Outlook2003: Display rules disappear (Tools/Organize/Colors/Automatic..)
Hi all, Some help needed re rules created in Tools/Organize/Colors/Automatic..: Those rules disappear randomly and without obvious reason. Some of them move to an other folder, where they are useless. Is there any way to save/restore those rules in order to avoid rekeying everything again and again? Where are they located? Are they accessible? Thanks for your help, Bibif they are stored in views - did you change views on the folder where you lost them? The best way to keep them is to copy the current view or make a new view and set the rules. -- Diane Poremsky [MVP - Outlook] ...

Substracting from a column
Hi, I have a column of numbers and I want to substract a certain value from every cell in that column and create a new column with new set of numbers. How do I do that? Thanks, Venus -- venus21pam ------------------------------------------------------------------------ venus21pam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30368 View this thread: http://www.excelforum.com/showthread.php?threadid=500201 Hi Venus Place the number you wish to subtract in a blank cell. Copy that cell. Mark the range of existing data, Paste Special>Subtract. or, in a ...

100% stacked column chart with negative numbers
How do you create a 100% stacked column chart with negative numbers? After I create the chart it only goes up to 80% How do you want it to show? The total length (including negative amount) of the column should equal 100%. (make sure you haven't limited the scale of your y-axis) What the graph is showing you is that the positive numbers do not account for 100% of your data. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Stuck" wrote: > How do you create a 100% stacked column chart with negative numbers? After I > create th...

Jon Peltier's code: can't delete initial series??
I wrote this on my machine running Win XP and XL2002. It worked. I gave it to a friend running Win2000 and XL2000, and it fails on the code to delete the initial series. What else can we do? Ed Sub MakeNewScatterChart() Dim wkb As Workbook Dim wks As Worksheet Dim LastRow As Long Dim LastCol As Long Dim colData As Long Dim cht As Chart Dim chtObj As ChartObject Dim rngData As Range Dim rngXval As Range Dim rngSer As Range Dim strTitle As String ' Get limits of data Set wkb = ActiveWorkbook Set wks = wkb.Sheets(1) LastRow = wks.Range("A65536").End(xlUp).Row LastCol = wks....

Highlighting Filtered column
I have a basic filter on an excel sheet. I would like to highlight the column in which the filter was selected. Can anyone think of a simple way to do this without having to resort to Macros (there are security issues at work, hence Macros can be awkward)? My first line of thought is to somehow detect when the filter has been selected and on which column and then use conditional formatting to highlight the column. However, I can't find a way to easily detect the filter status. Presumably in VB code I can find the filter and the appropriate property but I would rather not use a Macro. Tha...

How do I wrap text in the subject column in the list of tasks
i seeing the list of taks in using the menu "Tasks" in outlook. However I am unable to wrap the text under the subject column that I miss the words. Even when printed in landscape the words are truncated. So how do i wrap the text in the view and print out, so that I can see all the text. You can't, you can either remove and/or resize some columns or select the entire list and paste it into Excel and modify it from there. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads,...

deleted items reappearing!
Help. Many clients of ours are recently claiming that after they purge their deleted items folder (outlook 2003), they reappear soon afterwards. We tried it with cached mode and without (using exchange server 2003). The same thing happens in OWA. I saw some previous posts, but no one offered a solution. Any idea? Thanks. Anyone? Please? :-) "GC Postmaster" <gc_postmaster@gordon.edu> wrote in message news:%23cseSa3yGHA.4116@TK2MSFTNGP02.phx.gbl... > Help. > > Many clients of ours are recently claiming that after they purge their > deleted items folder...

IE8 installer starts automatically. IE8 is already installed.
I tried to help a neighbor who had filled up the hard drive on her 2002 HP computer. I uninstalled Arcsoft, which freed up 500MB. But during startup an IE8 install wizard starts automatically -- which I cancel. When I load IE to access the Internet, [help][about] indicates the current version is already IE8. Should I let the automatic installer continue? Does the attempt of IE8 installer to run automatically mean their is a problem? Advice will be appreciated, Stan Hilliard You may be confusing the IE8 "RunOnce" or "Welcome screen" with an attempt to (re...

Deleting a Company in GP 8.0
I wanted to delete a test company and recreate a new test company with the same ID. However, I accidentally deleted the company through SQL Server 2000's Enterprise Manager before deleting it within Great Plains. Now when I try to create the company in GP Utilities, I get an error message indicating 'This ID has been assigned to another company'. When I try to delete this company in GP, I get an error message indicating the database could not be located in 'sysdatabases'. How can I completely remove this database from GP? Thanks. -- Bob Bob, If you delete the D...

Stacked Column Chart #4
Date Event type Total 01-01-2006 A 10 01-01-2006 B 15 01-01-2006 C 12 02-01-2006 A 5 02-01-2006 B 7 02-02-2006 C 8 03-02-2006 A 5 03-02-2006 B 15 I would to generate a automatic stacked column (without having to mak manual selections) with the example above.The final result should b the date in the x axis and the column composed by the event typ (A,B,C) with the respective total. Note: the information is displayed as shown above. How can i do this? Thanks, Regards Andr -- vilacov ----------------------------------------------------------------------- vilacova's Profile: http://www.ex...

record not deleting from linked table...but it looks deleted.
Hi folks, I have an odd situation here. I have a table linked from my backend to the frontend that I have displayed through a subform. You can add/edit records no problem but when you delete the record it deletes it as requested and the record is gone on the subform. However if you go look in the backend (or even through the linked table), the record is still there. Anyone ever see this before? Thanks, Alan PS: This is Access 2002. ...

How to delete Autocomplete information in IE 6?
Hi All I am working on a small utility that will delete all the information(Temporary Internet Files, Cookies, History, Autofill, typed urls') related to IE as per the user requirement. I am having some problem deleting the autofill information as i dont have the required information. The information i have is as follows: Internet Explorer 6 On Internet Explorer (versions 4 through 6) AutoComplete web form information is stored in the Registry in the following hive locations: Encrypted usernames and passwords: HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\IntelliForms\SPW D...

Attach PDF to Account automatically
Is it possible to get the system to automatically pick up a file from a network folder and attach it to an entity (i.e. an account) Yes, through Custom code you can achieve that. you have to read using .net IO operations and then read account from MS CRM web services through CRM SDK and attach that. For that you need .net and CRM SDK Knowledge. -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "CRMTT" wrote: > Is it possible to get the system to automatically pick up ...

Help with columns #2
Hi How do I create a worksheet with just 3 columns (A, B, C...) rather than thousands (AA, BB, CC...)? Thanks! Hi - first there's a maximum of 256 columns :-) - You can only use these 3 columns (you can't delete the other columns) - if you like you can hide the other columns. Just select theses columns and goto 'Format - columns - Hide' -- Regards Frank Kabel Frankfurt, Germany Rich wrote: > Hi > > How do I create a worksheet with just 3 columns (A, B, C...) rather > than thousands (AA, BB, CC...)? > > Thanks! See one answer in m.p.e.newusers Please d...

Changes in columns from page to page
I have a documents created in Word 97 and now in Word 2003. They have 2 columns (It's a cookbook.) and I insert column breaks at the end of a recipe to force a complete recipe to start at the top of a column. On one particular document, I inserted a break at the bottom of column 2 on page 20 and the recipe pasted in column 1 of page 21 centered the recipe over both columns. The recipe is only about 24 lines of text. I've checked formatting, styles, page layout and anything else I can think of above and in other supposedly identical documents. I've created a templ...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Outlook Express Deletes Folders
Using Outlook Express. I went do delete an email from my created inbox folder (which then contains a number of sub- folders) by the usual "shift+delete" method and instead of bringing up the confirmation dialog that folder and all its subs just vanished! I have looked in the store folder and all the DBX files are just gone... I've just lost a few thousand emails from over 3 years, any ideas what has happend and maybe how I can get them back? Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlo...

Disabling automatic reply
Some time ago, I somehow added the ability to automatically reply with a certain message to all incoming mail. Like, Person X would send me an e-mail, then a reply from me would be sent without my consent, NOT containing the original message sent to me. I want to disable this, but I do not remeber how, I've gone through the Options menu...it's not a Read receipt or anything. Try sending me an email to see what I mean. I can't find any way to get rid of it... thanks in advance... On Tue, 14 Oct 2003 15:36:49 -0700, "Kurt" <kurtbt@shaw.ca> wrote: >Some tim...

Column Headings
Hi, I'm wondering if it will work or not. Your feedback would be much appreciated and give some example if possible. In a query, it has Column Headings and every year, I have to go and change from "1999","2000" and then change to "2002","2003" and remove "1999". Is there a form that has two text box where I can put 2002 and 2003 to replace 1999 and 2000. and put it in a view before generating the query? Again, your help would be much appreciated. Thanks If you are using a form in datasheet view with the query as the form's recor...

Pivot Table Column Widths
I have a pivot table that is part of a larger worksheet. when I choose something new from the drop down box, the column widths expand to the full width of the text instead of wrapping as I've formatted it to do. How can I keep the columns from expanding and ruining the format of the report? Set the pivot table to preserve formatting: On the pivot toolbar, choose PivotTable>Table Options Add a check mark to 'Preserve formatting', click OK Remove the check mark from AutoFormat Table Click OK Patrick_KC wrote: > I have a pivot table that is part of a larger worksheet. ...

Auto Hiding Columns
I want to auto hide a specific number of columns depending on a validation list selection. IE, user selects a shop ID and columns D, E, F, G, are hidden because there are 3 people in the shop. I created a button to accomplish this, but I want to automate as much as possible for ease of use. Is this possible? Fester You could use event code to hide the columns. For example, with a data validation list in cell A2 (with numbers 1-9): '=============================== Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then If Target.Value > 0 ...

Excel macro to specify rows and columns to include in printouts
Is there an Excel macro that simulates the effect of specifying the rows and columns to repeat in a printout? I am looking for something that simulates the following operations: 1) Page Setup->Sheet [Tab]->Rows to repeat at Top Highlight the rows 2) Page Setup->Sheet [Tab]->Columns to repeat at Left Highlight the columns Thanks, Gus Try something like With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "$A:$A" End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Softw...

Wrong message with cascading deletes
A2003: Table Person linked 1 X M to table Activity. The relationship has its Referential Integrity checkbox and the Cascade Delete checkbox both checked. When I delete a person all related Activity records are deleted which is to be expected. According to http://www.informit.com/articles/article.aspx?p=26115&seqNum=5 I should get the following message in this case: "Relationships that specify cascading deletes are about to cause 1 record(s) in this table and in related tables to be deleted. Are you sure you want to delete these records?" In stead I get: &quo...