PDA

View Full Version : Solved: Filter out dead columns



eddie5659
07-16-2004, 07:01 AM
Hiya

Thought I'd join here, as Dreamboat has helped me on other things to do with Excel :)

Now, I'm not a high end user of Office, but I have a little project at work to do. Its supposed to get me up the ladder, but I won't hold my breath :D

Its on Excel 2000, and first off we create a sheet that is imported data from an Intranet scource. This is converted to Excel, which is fine.

Then, I mess around with it, to enable me to filter out the rows I need, and leave out the junk.

The question I have is: I have loads of columns that in my eyes are a waste of time being there, and make the entire thing very confusing.

For instance, its numbered A-Z, then AI to AS, so quite a few.

Is there a way to creat some sort of program or macro, I think, to run so it will filter out the columns that are no good? I've looked at it, and think that 14 can go, but they're not all next to eachother, if you get my drift.

Considereing that the sheet before I even touch it is ~7000 lines, it does get to the old eyes.

If you need more info, I'll do what I can. Though, as its a work thing, its a Mon-Fri reply.

Thanks in advance

eddie

Zack Barresse
07-16-2004, 07:09 AM
Hi Eddie, welcome to VBAX!

So what distinguishes these columns from every other column on your sheet? Is there a specific row that is blank? Is the entire column blank? It must have some distinguishing characteristic that you can tell Excel to look at, and if it finds it, delete that column. And I'm assuming you want the other columns to the right shifted to the left? And are you wanting to do more than one sheet, or just the activesheet? Do you want a pop-up msg confirming the user wants to run the macro?

eddie5659
07-16-2004, 07:48 AM
Hi Zack

The columns that are to be removed all have different headings. They all start on row 2, but are named as follows, though this isn't all the ones that are there. If you want them, I can type it out. These are just the ones I want removing:

Column F = GC, G=Cntr, H=Whs, J=QtyAll, L=Uni, M=itm, N=B, O=Prod, P=Cat, Q=sts, R=ShelfOK, Y=QS, Z=BPC, AC=La, AE=Res

The top row isn't blank at first, but I'll insert a row, as I need to use it later on.

All columns have data in, though some have bits missing.

I'm a bit of a novice at asking Excel to do things. I know how to use LookUp, and just recently Contatinate, but this report has just been handed over to me, as a mini project, which is related to my normal work. I've also to 'clean' it up, and make it user freindly.

Yep, the shift of coulmns to the right is wanted.

Its just the main sheet, as once thats done, I can do my normal edit/paste to others.

Not sure about the popup, but if its easy to do, why not :)

Thanks

eddie

Zack Barresse
07-16-2004, 08:13 AM
Another question, you seem to know exactly which columns hold these figures, will they be the same columns every report? Or change columns but have these same headings?

eddie5659
07-16-2004, 08:25 AM
They will be the same columns for this report each time.

The column data will change each time, so that eventually it'll be a lower amount of rows (its an expired list of all stock in the world, that I have to clear). However, the names of each column will be the same, unless they update our online database, but I can't see that happeing for the next few years.

Logging off now, as I'm going home. I'll try and reply through the weekend, if I don't actually need the report in front of me. Otherwise, I can reply better on Monday.

Thanks

eddie

babsc01
07-16-2004, 08:28 AM
Eddie...maybe filtering out the source of the data can solve this for you. If you're pulling in data from an external source, such as an Access or SQL query, you should be able to remove the extraneous columns from the query itself. Then, once the conversion to Excel is completed, you should only see the data you need.

Hope this helps...I do this constantly every day. Ain't it fun?

Zack Barresse
07-16-2004, 08:37 AM
Eddie,

I'd personally go with what babsc01 is suggesting, as it'd probably be easier and much cleaner that way. If you're still looking for an *after-marker* solution, that can be done too. Hope you have a good weekend! :yes

mdmackillop
07-16-2004, 11:44 AM
Hi,
This should delete all columns :006:where the value in row 2 is listed in the array.
MD

Sub RemColumns()
Dim RemArray
RemArray = Array("GC", "Cntr", "Whs", "QtyAll", "Uni", "itm", "B", "Prod", "Cat", "sts", "ShelfOK", "QS", "BPC", "La", "Res")
Range("IV2").Offset.End(xlToLeft).Select
Do
For Each Rm In RemArray
If UCase(ActiveCell.Text) = UCase(Rm) Then
ActiveCell.EntireColumn.Delete
Exit For
End If
Next
On Error GoTo Exits
ActiveCell.Offset(0, -1).Select
Loop
Exits:
End Sub

parry
07-16-2004, 07:51 PM
Hi, just for fun heres another one with a different approach to avoid looping thru the array.

Sub DeleteUnwantedCols()
Dim BadOnes, Rng As Range, c As Range
'Create a string based array
BadOnes = "{""GC"", ""Cntr"", ""Whs"", ""QtyAll"", ""Uni"", ""itm"", ""B"", ""Prod""," & _
"""Cat"", ""sts"", ""ShelfOK"", ""QS"", ""BPC"", ""La"", ""Res""}"
'Presuming header is in row 2
If IsEmpty([IV2]) Then
Set Rng = Range("A2", Range("IV2").End(xlToLeft))
Else
Set Rng = Range("A2:IV2")
End If
'Loop thru range
For Each c In Rng
'eg pretending cell value is Whs this is the same as this formula
'=SUM(IF({"GC","Cntr","Whs"... etc}="Whs",1,0))
If Evaluate("SUM(IF(" & BadOnes & "=""" & c & """,1,0))") > 0 Then c.EntireColumn.Delete
Next c
End Sub

byundt
07-16-2004, 08:38 PM
I assume that you want to delete from row 2 down (leaving row 1 in place). If so, here is a macro that deletes all the cells at once. This code uses the letter designations for your columns rather than searching for text or headers--so it might be easier for you to modify later.

Sub DeleteColumns()
Dim rg As Range
Set rg = Union(Columns("F:H"), Columns("J"), Columns("L:R"), Columns("Y"), Columns("Z"), Columns("AC"), Columns("AE"))
Set rg = Intersect(Rows("2:65536"), rg.EntireColumn)
rg.Delete shift:=xlToLeft
End Sub

Anne Troy
07-18-2004, 10:59 AM
LOL!
Eddie...looks like you're in good hands, dude.

Remember...when wanting VBA, it's always VERY helpful for the coders to have a sample file in which to work. If you don't want to provide the exact data, then just delete all but a hundred rows or so, change any data necessary to protect the innocent, and load the file up here (zipped).

It takes a lot less time to get your answer too.

Welcome to VBAX!

Guys--Eddie is a terrific PC "security" guy. If you have any questions about that stuff, go ahead and ask him!

eddie5659
07-19-2004, 04:28 AM
Hiya

Back after a relaxing weekend :)

babsc01 and Zack, I can't edit it at the main source, as we're a global company, and this report that is downloaded that I need the columns removing, someone in another country may need them.

I will say one thing: how do you get the code that you have all written to work in Excel? What I mean is, I've never used macro's or anything before (treat me as a newbie on that part)

Okay, I'm going to upload a document, but I have cut the rows down. Also, once this is solved, I have another question, but not sure if it can be done.

eddie

babsc01
07-19-2004, 07:14 AM
Eddie...I understand not being able to edit the query. Does the data come to you in an Excel sheet? If not, from where do you import it to Excel, and by what means?

Zack Barresse
07-19-2004, 07:18 AM
Ok Eddie, I've attached a zipped version of your file. It has all three macros presented thus far (in this thread) installed on there. You can see them by, with the Excel file open, hit Alt + F11 to open the Visual Basic Editor. The green comments (text preceeded with a single apostrophe ' ) specify certiain things the coder wishes to say about his/her work.

Personally I'd go with MD's the RemColumns routine. I experienced problems with Parry's and Brad's took a much longer time. MD's was all around satisfactory.

And post any question you've got, I'm sure it can be done!! :)

eddie5659
07-19-2004, 07:58 AM
Thanks Zack. Just tried the Alt F11, and noticed its greyed out, so can't access it. Ah, got into it this way:

Tools | Macro | Macros. Then, clicked on the one I wanted to look at, then pressed Edit. Is that the same sort of thing?

Will look at all individually. It looks like MDs and byundt produce similar results.

babsc, its originally from an online database, which is where we pass/fail all batches of product, worldwide. This database is linked to another database, which contains locations/amounts. Then, I click on a link in our Intranet, which collects both bits, and then I have the task of going through it. The programmer is based in Switzerland, and I'm nowhere near up the ladder to ask him, still on the bottom rung with that part :D

eddie

Zack Barresse
07-19-2004, 08:04 AM
If you press Alt + F11, that will open the VBE. From there, find your open file in the left hand pane (if not there, press Ctrl + R, opens the project explorer), expand the Modules folder, double click Module1. This will bring up the edit window and you'll be able to see all the code in that module. HTH

eddie5659
07-19-2004, 08:09 AM
Just looked at Brad's, and the thing is, the columns may change, as in the numbering. F could become E, if one of the columns is removed over time. This will then remove an important column.

That's if I'm reading that correctly.

Am I assuming that if I wanted to add another column, if I look at MD's, it woyld be something like:

RemArray = Array("GC", "Cntr", "Whs", "QtyAll", "Uni", "itm", "B", "Prod", "Cat", "sts", "ShelfOK", "QS", "BPC", "La", "Res", "another column")

And removing one from the list, I just delete it.

The reason I ask, is if you all look at the file, there are a few coulmns with dates/times on. I know so far I use 2, but may not need the others, which can tidy things up even more.

eddie

eddie5659
07-19-2004, 08:21 AM
Just found out I have it disabled, so getting in touch with IT :(

No idea why, as my manager says it should be on

eddie5659
07-19-2004, 08:39 AM
grumble, grumble

Okay, just spoke to IT, and its the typical help desk person, don't know what it is, why has the manager got it, blah, blah.

Anyway, as the macro can be seen in the editor, and my boss has explained you can write macros using the Record button, if I choose MD's, how do I incorporate that into my Excel from basics?

I assume there is a macro folder somewhere. I do appreciate this help, and if need be, my boss will pop over, and help on setting it all up :)

*edit*

Think I've figured it out. If I get the one macro (may delete the other two to create just the one), I click on File | Save. Then, to run it, open up a new sheet with all columns intact, Tools | Macro | Macros, and click on the one and click Run.

Is that right? If so, I'll have a go tomorrow :)

eddie

byundt
07-19-2004, 09:15 AM
To install a macro, ALT + F11 to open the VBA Editor. You can then use the Insert...Module to create a new module sheet. Paste MD's code there, then ALT + F11 to return to the spreadsheet.

To run a macro, ALT + F8, the select the macro and click the Run button.

You may encounter a problem with macro security preventing the above steps from working. If so, open the Tools...Macro...Security menu item and change the security level from High (its default) to Medium.

Zack Barresse
07-19-2004, 09:28 AM
Another option to run it, would be to press Alt + F8, choose desired Macro, press Options and assign it a shortcut key (eg K or M). Then, every time you run that code you may do so by pressing Alt + K (or whatever key you assinged it.

You can make this even more *global* by saving the macro to your Personal.xls file, which is Excel's global macro. This means you can call these procedures from any workbook w/o having to re-input the code into each book. One time for any file on that machine. :) If you don't have a personal.xls or would like help setting one up, post back, there are a couple of ways.

mdmackillop
07-19-2004, 10:04 AM
Hi Eddie,
As an option, the array could show the columns you wish to keep, with all others deleted, if this would be easier to manage, please let me know.
MD

parry
07-19-2004, 02:22 PM
I experienced problems with Parry'sNo problem with recommending another macro as mine was more to show a different appoach using an array. Theoretically it should be the fastest operation as it avoids a second loop and selecting cells. However it would be hardly noticable in such a small dataset (max 256) so stick with MD's solution.

For my personal understanding Zack could you advise what problems you had as it worked fine for me.

Zack Barresse
07-19-2004, 03:33 PM
Sure Parry, it didn't delete all of the specified columns. I don't have the macro on file, but the first one in the array (BadOnes) it didn't delete, for me, was ""Cntr"". I'm not sure why this happened, it may have been me. I just tried a copy/paste and may have gotten something bungled up in the process, so it could have very well been me.

As a side note, it was the fastest too. :)

eddie5659
07-20-2004, 02:43 AM
Here's the latest:

I've gone into Macro's, and then Edit. I removed the other two codes, and saved it. Looking in the Macro options, the Macro is in All Open Workbooks. Is that the same thing as you mentioned, Zack?

How do I go about changing the title of the macro? At the mement it's called eddiesTest.xls!RemColumns. Going to call it RemoveColumns.

Ah, just looked to where it saves, the Temp folder :(

I don't have a personal.xls, though as I'm on a network, I'll place this in my Profile.
Can you help me create one?

eddie

eddie5659
07-20-2004, 09:00 AM
Created my personal.xls by following this:

http://www.personal-computer-tutor.com/personalxls.htm

:)

Just need to figure out how to rename it (unless I can do it in Windows Explorer) then I can save it there, I'm thinking :)

eddie

Zack Barresse
07-20-2004, 09:21 AM
You can rename it if you'd like, although it's usually strongly recommended against doing so. It's in your XLSTART folder. Find that and you find your personal.xls file. Those files will start automatically. Just be sure of what you want to name it, especially if there is a possiblility of other users coming onto your pc in the future, etc.

I'm glad you got your file!! That's awesome!

Anne Troy
07-20-2004, 09:23 AM
OMG, Eddie. I used to work with her!! Thanks!!

mdmackillop
07-20-2004, 09:25 AM
Hi Eddie,
To create a Personal.xls, go to Tools/Macro/Record New Macro; store in Personal Macro Workbook. OK ; then click in a cell and press the stop recording button. Close Excel and youre Personal.xls should be saved. Reopen Excel, Press Alt+F11 and you should see the Personal.xls project
MD

eddie5659
07-20-2004, 11:39 AM
Zack, when I said to rename it in Explorer, thats because the title is RemColumns. I'll leave it as it is, as I may only have a few macros in there, and I have to write a method on how to edit/filter the report anyway, so can include the names.

Will do some messing tomorrow when I'm at work, to get it all working okay. And when it is, I'll start a new thread with those other questions, and tell you when this is Solved :)

MD, thanks for that. I created it using the link above, which was a different way. I assume its the same thing.

Dreamy, its a small world :)

eddie

Zack Barresse
07-20-2004, 12:06 PM
I assume its the same thing.


You got it. :) Personally, I'm of the opinion, that the way MD has described is the easiest and fastest way to create an instance (you only need one) of a personal.xls. Imho.

eddie5659
07-21-2004, 01:14 AM
Hi Eddie,
To create a Personal.xls, go to Tools/Macro/Record New Macro; store in Personal Macro Workbook. OK ; then click in a cell and press the stop recording button. Close Excel and youre Personal.xls should be saved. Reopen Excel, Press Alt+F11 and you should see the Personal.xls project
MD
I've created it like you said now, as I can't seem to save the blasted thing. However, I still can't click Alt F11, so going to Windows | Unhide.

Back soon

Anne Troy
07-21-2004, 01:16 AM
Alt+F11?
Eddie...do you have a wireless keyboard? If so, hit the FLock key!
Then try Alt+F11...

eddie5659
07-21-2004, 01:33 AM
Yes, its all working :dance:

What I have done, and this is so I can bookmark this thread, is this. Deleted the personal.xls that I created. Saved it to the wrong place, as the link I provided is based on a standalone pc. Mine is now in:

C:\WINNT\profiles\eddie\Application Data\Microsoft\Excel\xlstart

Then, I don't know if this is the correct way or not, but its working when I shut Excel down, and reopen:

I went into the Excel file that Zack sent me, and Tools | Macro | Macro. Then, I clicked on the one I wanted, deleted the other two, and then highlighted, and copied. I then opend the actual report I'm working on, Edit in Macro, and pasted the code. I saved it, closed down, saying Yes to Save.

Also, my personal.xls opens all the time now, no need to unhide.

Thanks ever so much, all of you. :)

I'll post a new thread soon, but also waiting for a new meeting to find out if there's more.

*edit* No wireless keyboard, but I think its because I'm on a network, and they've disabled it for some reason.

Thanks

eddie

eddie5659
08-09-2004, 08:42 AM
Hiya

I know this one is solved, but re-running my report from scratch to help on the new questions, and my Macro doesn't work :(

Has it been changed?

Sub RemColumns() 'MD
Dim RemArray
RemArray = Array("GC", "Cntr", "Whs", "QtyAll", "Uni", "itm", "B", "Prod", "Cat", "sts", "ShelfOK", "QS", "BPC", "La", "Res")
Range("IV2").Offset.End(xlToLeft).Select
Do
For Each Rm In RemArray
If UCase(ActiveCell.Text) = UCase(Rm) Then
ActiveCell.EntireColumn.Delete
Exit For
End If
Next
On Error GoTo Exits
ActiveCell.Offset(0, -1).Select
Loop
Exits:
End Sub


If not, I'll have a bit of a delve to see whats causing it.

Thanks again

eddie

Zack Barresse
08-09-2004, 09:42 AM
Hey Eddie,

What is not working? Can you step through your code? Do this by going to the VBE (press Alt + F11), bringing up your relevant code module and putting the cursor inside your routine somewhere and press F8 to perform one line of code at a time. You can minimize your VBE window to half screen and watch Excel in the background to see what goes on. Does it fail at a certain line? If so, which one, and what error do you get? How are the results not as expected?

eddie5659
08-10-2004, 02:06 AM
Hiya Zack

It doesn't fail, all lines that highlight are in yellow. It does loop on this bit:

If UCase(ActiveCell.Text) = UCase(Rm) Then
ActiveCell.EntireColumn.Delete
Exit For
End If
Next

Though is it supposed to? It goes If.., End..Next.. and then back to If, as in the start of the lines.

What its not doing, is removing the columns, eg GC, Cntr, etc.

This is a brand new sheet. I can't change the security settings (greyed out).

Thanks again

eddie

mdmackillop
08-24-2004, 01:53 PM
Hi Eddie,
Is the sheet protected?
MD

Zack Barresse
08-24-2004, 02:06 PM
Maybe I'm not understanding. Do you not want it to exit the loop upon finding a matching condition (matching text)? If so, take out the Exit For portion. And sheet protection can be worked around easily enough, if that's hindering you.

mdmackillop
08-24-2004, 11:54 PM
Hi Eddie, Zack,
The loop exits when a match is found, if you remove the "Exit For", it will continue to check all array elements, slowing (slightly) the routine.
MD

Hammer300
08-25-2004, 02:57 PM
Hiya Eddie,
Sounds as though your on a network as I am in my work place???? I am locked out of the VBA Editor and I had a similar problem and all I did was hide the columns cause the data from the source is updated all the time.
Dont know if that will work with yours or not.
It does say solved but ohhh well.

Rob

eddie5659
09-20-2004, 02:37 AM
Hiya

I haven't forgotten about this thread, its just that the Swiss have gone live on a new database, and the report was moved, and the heading's changed, which could be the reason.

Plus, I'm a little behind with it, as many of the managers above won't sort the problems out, so trying to get on top of it all :D

It will be a few weeks, plus I'll edit the macro with the new headings, to see if it'll work :)

Thanks everyone

eddie