Complex macro? or Multiple macros? Manual? CHALLENGE!!

Try this

Please Login or Register  to view this content.

You could send me your spreadsheet and I could send you my Paypal details ...

You're not asking for help creating a formula or fixing a coding error. This is a mini-project. Not difficult in concept but a few hours work to code and test.

You could, of course, record a macro while you carry out the task(s) manually. Then come to the forum for help tidying and tweaking the code.

If I were doing it, I'd probably set up an array of columns to be deleted and loop through that. The array could be hard coded or defined on a "control" sheet.

Deleting rows could be done using Autofilter to select the rows and deleting them.

Regards, TMS

THANK YOU MRICE!

So when I run this, I get "For control variable already in use" at:

For N = Cells(Columns.Count, 1).End(xlToLeft).Column To 1 Step -1

Any ideas?

Hello

It appears MRice left out a "Next N" statement. I have added it in the code below.

Please Login or Register  to view this content.

Hello kamgrn,

Do the statements appear individually in the cell or in combination with other words?

Hello kamgrn,

Are these labels in row 1 of the columns?
Are there any merged columns or rows?
Can you post a copy of your workbook?

To Attach a File:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

So maybe:

Please Login or Register  to view this content.

I know this thread has been closed, but I am keen to know and understand why Mrice’s has not worked.
I think the reason could be this line of code:

Please Login or Register  to view this content.

If step over the code using F8, assuming n as long shows one column only, despite there are appear to be over 30 columns. The code looks at cells(1,1) and ends as it could not find any word which matches with the select case. To test it, I have added "ZipCode" in to the case statement and run the code. It deleted column “A” as the code find "ZipCode" in cells(1,1).
I have also run some msg box

Please Login or Register  to view this content.

N returns 8
So, the question is why the code returns a single column? Is it because the columns are merged in to one column, i.e. Column “A”?
I also run unmerge code and there is none.
May be as a newbie to VBA, I still do not understand how the last columns work.
I had expected

Please Login or Register  to view this content.

Row should show the last row and

Please Login or Register  to view this content.

should also shows the last column.
Back to the drawing board then!

Please Login or Register  to view this content.

The format for Cells is Cells(Row, Column) so that statement is starting in column A (1), row whatever. The Case statement also has a leading space for all the headers except the first. I suspect that's not really the case.

TMShucks!
You have an eye for details. I written half a page on a single line as I was unable to spot a single error. I thought I was gone made. I am not sure if Jerry and Leith had spotted this error.
It should have been like this:

Please Login or Register  to view this content.

Now, I can see more than 30 columns, ie 87 columns. The code works too.
I do not think you need more than 5* on your name as you have already got them, but will double them if you wish.
Thanks all

@AB33: Thanks for the rep, and the kind words.

I said, in my initial response, that you can spend a few hours developing *and testing* something like this.

The OP said there were/are 80,100 rows of data. The code in post two loops through the rows twice, once on column BN and once on column W. That could take a while ... I think that would be better done with an Advanced Filter (that I'd probably create on a hidden sheet) and delete the filtered rows.

Then use JB's line of code to delete the columns, assuming they are fixed. if they're not, I'd list the ranges in a (hidden) worksheet and build a range variable and delete that.

With only half a dozen rows of test data, it's not really feasible to determine how quick or slow the code will be in real life.

Regards, TMS