Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 42

Thread: Solved: Filter out dead columns

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location

    Question Solved: Filter out dead columns

    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

    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

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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?

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    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
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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?

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    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
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  6. #6
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    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?

  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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!

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    A RemoveColumns macro

    Hi,
    This should delete all columns where the value in row 2 is listed in the array.
    MD
    [VBA]
    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
    [/VBA]

  9. #9
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi, just for fun heres another one with a different approach to avoid looping thru the array.

    [VBA]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 [/VBA]

  10. #10
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    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.

    [VBA]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[/VBA]

  11. #11
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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!
    ~Anne Troy

  12. #12
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    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
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  13. #13
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    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?

  14. #14
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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!!

  15. #15
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    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

    eddie
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  16. #16
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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

  17. #17
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    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
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  18. #18
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    Just found out I have it disabled, so getting in touch with IT

    No idea why, as my manager says it should be on
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  19. #19
    VBAX Regular
    Joined
    Jul 2004
    Location
    Bradford, England
    Posts
    17
    Location
    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
    Just go with the flow, like a twig on the shoulders of a mighty stream


    http://forums.techguy.org

  20. #20
    VBAX Regular
    Joined
    May 2004
    Location
    Springfield, MO
    Posts
    39
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •