Filter out dead columns

**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

Originally Posted by firefytr

I experienced problems with Parry's

No 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!

**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

Originally Posted by eddie5659

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

Originally Posted by mdmackillop

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

**eddie5659** · 07-21-2004, 01:33 AM

Yes, its all working

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

**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.

Thread: Filter out dead columns

Thread Tools

Display

Posting Permissions