Delete multiple columns if they fit different criteria

[duugg]

Hello all,

Okay, this one should be interesting...(probably cake for you guys )

Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.

These columns below are all in row A (the title row)
If column named "paper" says "white" OR "grey" AND
column named "pizza" says "mushroom" AND
column named "size" says "large" AND
column named "flavor" says "butter pecan"..
(again, only going down to the same matching cell with any data in column a)

Then delete these columns

Thanks much!

[JBeaucaire]

First, there is no Row A. You mean row 1?

Your description is confusing. when someone says "Delete these columns" I read that as the entire column M is deleted, or column A is deleted and everything shifts to the left. That's not what you mean is it?

You mean that if data on ONE row all match the criteria in specific columns, then you want that ROW of data deleted?

So the row with all the matching criteria White-Mushroom-Large-Butter Pecan...that row is removed and then we continue looking for matches?

So mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

[duugg]

Ahh, sorry for the confusion. MUCH SIMPLER REQUEST HERE....


1. Find the column named "paper" (It will be in the title row, row 1)
2. Look down that column to see if EITHER the text "white" or "grey" is in each cell.
3. IF...EITHER, the text "white" OR "grey" is in each cell in the "paper" column, then delete the ENTIRE column, yes, I do mean ENTIRE column.

To clarify further... if the "paper" column is currently in column D and the "pizza" column is currently in column E, then AFTER the macro is run, the ENTIRE "pizza" column would now be column D, because I have eliminated the ENTIRE "paper" column.


HOW FAR DOWN THE COLUMN TO RUN THIS MACRO?....SEE BELOW...

The only problem I have at this point is to explain HOW FAR down the column that I want the macro to look for this text. Naturally, I don't want it to go down to the very last row that Excel 03 limits me too (64,000 rows?) as I will never have data down that far.

So..................
I will always have another column named "customer number", that column will always have data in the bottom-most row.

In other words, if the "customer number" column has data down to row 1500 and stops there, then the entire worksheet will not have any data in row 1501, 1502 etc.


Thanks...this should be much easier now

[JBeaucaire]

So, WHITE/GREY has to be the value in every cell to qualify for deletion of the column.

Please Login or Register  to view this content.

The first sample column above qualifies, the second does not, correct?


If you posted sample sheets, this would make it much easier on us to create a macro, and far less questions, I'd bet.

[duugg]

YOU NAILED IT! YES!

That's exactly what I what. If "dog" were in the second column, then leave both columns intact.

Thanks

[JBeaucaire]

Um, the two columns weren't meant to be a single example, I was suggesting one wouuld be deleted and the other would not, using the same macro at different times.

Anyway, something basic to give you the idea:

Please Login or Register  to view this content.

I suppose instead of the Case Else setting the "FALSE" parameter, we could bail out completely with an "Exit Sub"...

[duugg]

JB,

It worked with one exception...

If there is a text entry in the "customer number" column in row 200 and the last text entry in the "paper" column only goes to row 199, the "paper" column is still deleted, which it shouldn't be.

The text "white" or "grey" must go down to the same row# as the bottom-most cell in the "customer number" column.

Thanks for all your help on this, we're almost there!

[JBeaucaire]

I can't see your sheet from here, so I have no idea what to change. A lot of handholding required when working blind...hehe.

[duugg]

JB,

thanks again for the help, I want this macro so bad, I'm even working on this one "off the clock"! hehe. Thanks again...

This should convey what I want...

Please Login or Register  to view this content.

In example 1 above, the macro should delete the paper column because it fits 2 criteria

a) the text "white" or "grey" is in the "paper" column
b) the text "white" or "grey" goes down to the bottom-most row as the "Customer Number" column

Please Login or Register  to view this content.

In example 2 above, the macro should NOT delete the paper column because it only fits 1 of the 2 criteria

a) Is the text either "white" or "grey" in the "paper" column? >>> YES

b) Is the text "white" or "grey" present in each cell in the "paper" column going down to the same bottom-most row as the "Customer Number" column (in this case, row 5) ? >>>NO

If you look at row 4, you see that the cell in the "paper" column is blank in row 4, but there is indeed a customer number in the customer number column. So, therefore, the column should NOT be deleted because there is incomplete data in the "paper" column.

If you could also highlight in RED any cell offenders in the "paper" column, that would be great also.

Thanks much!

[JBeaucaire]

Let's try this out:

Please Login or Register  to view this content.

[duugg]

JB,

Yes yes yes, that's it! (with 1 little tweak)

Could we eliminate the msg box that comes up (paper test succeeded, paper test failed, etc) so that it will not interrupt the rest of the macro from running (this macro will be in the middle of other macros) and just simply highlight the offending cells in the paper column so that the rest of the macro can finish?

Thanks

[JBeaucaire]

Of course. Just remove those lines. They have no substantive effect on the macro other than to tell you what happened (or didn't). If you'd prefer the mystery, just remove those lines.

[duugg]

What code changes/additions are needed to highlight the offending cells in the "paper" column rather than the "customer" column?

Thanks

[JBeaucaire]

I think this line:

Please Login or Register  to view this content.

[duugg]

Hmm, well, the code...

Please Login or Register  to view this content.

...will only work if the "paper" column is in column b. The "paper" column# will rarely be in the same column#. So can we point the above code to say this instead?

Please Login or Register  to view this content.

P.S. My thought is right but I know the syntax is wrong

Thanks

[JBeaucaire]

Ah, you're right. Apologies...a bit distracted by "work" today.

Please Login or Register  to view this content.

Does that do it?

[duugg]

WWWOWW!

This is great stuff! Kudo's JB!

2 things and I think I'm done with this macro, whew!



Question 1.
What line of code needs to be added (and where to add it to) that will highlight all the COMPLIANT cells in the "Paper" column dark grey?




Question 2. I noticed in the macro that you had this code for case sensitivity

Please Login or Register  to view this content.

How can this code be modified to ignore case sensitivity altogether?

Thanks soooo much!

[JBeaucaire]

1) Not sure what we gain here, but easy enough to do.

Please Login or Register  to view this content.

2) ??? the code I added makes the macro case-insensitive, that's why it's there. If you put in WHITE, white, wHiTe or WHITe...they'll all evaluate to "white" by the fact that I convert it all to lowercase before I evaluate it.

So it IS case-insensitive now.

[duugg]

JB,

The Case Sensitive/Insensitive issue
Okay, the case thing. I plan on modifying this AWESOME macro to apply to many other columns (as a matter of fact, I have already done so and it worked!)

BUT...the problem is. If someone else uses this macro and changes their words from "grey" and "white" to "Blue" and "Red" they would modify this line of code to do so...

Please Login or Register  to view this content.

The macro will not run unless they know that they have to change "Blue" to "blue" and "Red" to "red". So, in a way, it's not truly case sensitive.

Is there any line of code that can be changed so that if another person were to modify this macro, it wouldn't matter whether they used "Blue" or "blue" or "Grey" or "grey". Is that possible?


Why do I want the compliant cells highlighted grey?You've been such a great help, I feel I must satisfy your curiousity as to why I would want the compliant cells highlighted. When I get my data, before the macro is run, I can have thousands of rows in which I must analyize data for intregrity. I imagine that my first run of the paper macro will not delete the columns because it won't fit the criteria, which is what I want. So, when looking at large portions of data, it's easy to skip over the grey cells (I plan on modifiying this macro for several other columns) and focus on the white cells (which don't have a macro) and of course, the naughty red cells.

Thanks

[JBeaucaire]

Case insensitivity is accomplished by adding what I have added. Anyone in there making CODE changes pretty much needs to know what the functions do and how to use them, else they'll learn the hard (like most of us have). At worst, it won't work and they'll learn something new.

So, either train your users, or trust they'll figure it out. We all do.

[duugg]

Okay, ALL is good. This thread is now officially closed.

Thanks for all the help on this one JB! It was certainly a very interesting and great learning experience for me.