Amending macro code - Deleting and moving columns

I have the following code that deletes a lot of columns, copy and pastes one into column B and then sorts by column B.

Please Login or Register  to view this content.

I now need to include the previously deleted columns M & O and changed the code to show this....

Please Login or Register  to view this content.

.... and this also changed the column I needed to cut and paste to K....

Please Login or Register  to view this content.

... but it isn't working. I do not understand the rest of the code as I didn't create it. The macro simply deletes all records !

Can anyone please help ?

The last bit of code looks at column B and if the first two characters in column B do not equal 29 then the entire row is deleted.

The code looks ok. It would probably be better if you explained in simple English what you are attempting to do and then let us analyze the code to see if it is in fact doing what you want. As it stands it appears to not have any errors.

I work at a hotel and I have a report that has data in columns from A through AO. I want to delete everything except A,B,E,F,G,H,I,J,M,O,P,Q & AL. Then I wish to move the room number column, which after the delete is in Column K, to Column B. Then I wish to sort the records by the room number in column B starting with, in the case of the code above everything starting with 29.

This code works perfectly. But now I have been asked to keep columns M & O which the original code deleted and I cannot work out why it isn't working with the changes I've made.


The next step, which I was going to look at later is as follows......

Once I sort out the issue above is the create a macro (15 in total) for each section of the hotel. We have 3 & 4 digit room numbers and a different person is responsible for different sections and as such requires a different version of this report. This code runs for the 2900 section. I will also need to code for each of the following....

3 digit room numbers starting with

{1 & 2},{3,4 & 5},{6 & 7},{8,9,10 & 11},{12,14 & 15},{16 & 17}

4 digit room numbers starting with

{25 & 26},{27 & 28},{29},{30},{31},{32},{40,41,42,43 & 44},{51 & 52},{53,54 & 55}

Hope that makes sense.

Attached is a before and after. (Before the following code has been completed)

Please Login or Register  to view this content.

Will look at later today when I have time available if no one has already supplied a solution.

Hi M12NIX

What do these 3 Digit Room Numbers look like...or are they actually 4 Digits?

10 & 11},{12,14 & 15},{16 & 17}

Hi M12NIX

I understand this...what I don't understand is what do these 3 Digit Room Numbers look like...
{1 & 2}...to me this says from 100 to 299


Versus these 3 Digit Room Numbers...
{12,14 & 15}...I don't know what this says to me...120 to 129??? 140 to 159??? These overlap with the above.

Show me what the Room Numbers look like in your grouping.

So, these are 4 Digit Numbers...
1001-1019,1101-1119,1201-1219,1401-1419,1501-1519,1601-1619,1701-1719

Hi M12NIX

Try this Code on your Raw Data...I've manufactured a Raw Data sheet (Before) and it appears to work for me.

The Code creates a separate Worksheet for each Grouping. CTRL + x will fire the Code.

Please Login or Register  to view this content.

Hi M12NIX


It'll take a bit...perhaps not tonight.

Hi M12NIX

You'll need to play with this...been tested but not thoroughly.

Please Login or Register  to view this content.