Delete column based on header selection

[Eric Carolus]

Hallo All!

I have searched low and high buy could no find what I was looking for.
I have a named (dynamic) range called "Rooms" that contain venues, room numbers (1,2,5, 7) and text (Hall, Lab, etc).

I wish to manually select the Header (e.g. 1, or 2 or Hall), I want VBA to extend the column from the header (selection)
down by 10 rows, delete that the resulting column, and shift all columns to the left.

I thank you in advance.

Eric

 

[MARK858]

Do actually mean "down by 10 rows, delete that the resulting column" as in the whole column or do you mean delete the 11 cells or do you mean something else?

Btw, I am not getting the significance of the named range in your question as you are manually selecting the header.

 

[Eric Carolus]

Hi

Yes I mean the header PLUS 10 rows. I guess you are right, we can ignore the named range.

Thanks

Eric

 

[MARK858]

If you mean delete the header + 10 rows then

Selection.Resize(11).Delete Shift:=xlToLeft

if you mean the 10 rows below the header then

Selection.Offset(1, 0).Resize(10).Delete Shift:=xlToLeft

 

[Eric Carolus]

Hallo MARK858

Thank you for the advice , but I could not make it work.

I am now going to include the workbook.
Its has two sheets (Sheet1 & AddRooms) and three buttons (AddRooms, Delete Rooms & Specific Removal) on Sheet1.
AddRooms & Delete Rooms Add rooms (to the Last column) and Delete rooms (remove the last Column) respectively.

I wish to change the Delete rooms (instead of deleting the Last room added) to the DELETING the room that I SELECTED in the HEADER (that could be any room).

I had been at this almost all day, too no avail.

Thanks you to all of you in advance.

Here is the link to the workbook: https://www.dropbox.com/s/qy1gduz7bijiyq3/insert a column.xlsm?dl=0

Eric

 

[MARK858]

When I put the code as posted in a regular module, unprotect your worksheet, select the header and run the code it deletes the column fine.

 

[Eric Carolus]

Hi MARK858!

And you are sooooo right! It works just fine.

My problem? forgot to Unprotect the sheet!

Thanks once again!

I really appreciate it.

Eric

 

[MARK858]

Happy you got it sorted. Btw, as your data is 10 rows including the header it should be...

Worksheets("Sheet1").Unprotect "myPassword"
Selection.Resize([B][COLOR="#FF0000"]10[/COLOR][/B]).Delete Shift:=xlToLeft
Worksheets("Sheet1").Protect "myPassword"