Delete Column only after certain Row with Specified Header

Hi,

I am looking to delete the entire row of a data from row 9 and below while keeping the format of the boxes I have above row 9. Right now after my macro runs, the boxes shrink as well because columns have been deleted.

Also, I'm not sure why my macro with specified headers is not deleting some of the headers that is not from the list.

I took the macro from this thread here and made some minor tweaks to it on my own: http://www.excelforum.com/excel-prog...d-headers.html

Please see my attached file for a more clear description.

Any help would be greatly appreciated!

Thanks!

DelColumnHeader.xlsm

Hi,

Here is the formula I used in the workbook:

Sub DelColumnNotInList()
Dim LastCol As Double
Dim ColCtr As Double
LastCol = Cells(9, Columns.Count).End(xlToLeft).Column
For ColCtr = 1 To LastCol
If InStr("Name Title Age Position ID", Cells(9, ColCtr).Text) = 0 Then
Cells(1, ColCtr).EntireColumn.Delete
ColCtr = ColCtr - 1
End If
Next ColCtr
End Sub


I tried using clear-contents, but I would actually like the columns removed and to have those columns shifted as it would if the column was deleted, but just that the Box Shape I have would also delete as well.

Please Login or Register  to view this content.

Originally Posted by AB33

Please Login or Register  to view this content.

Thanks! This is exactly what I needed!

When I run it though, the Headers with "A" and "D" are still there, even though I know the macro doesn't have it listed as one of the headers.

Do you know how that could be fixed?

Originally Posted by AlphaFrog

• Right-click on each box shape and select Format AutoShape from the pop-up context menu.
• Select the Properties tab on the Format AutoShape dialog.
• Select the "Don't move or size with cells" option and OK.

Thanks! Had no idea you could do that... So simple!

From my code I pasted above, do you know why my headers "A" and "D" are not deleting?

Originally Posted by AlphaFrog

• Right-click on each box shape and select Format AutoShape from the pop-up context menu.
• Select the Properties tab on the Format AutoShape dialog.
• Select the "Don't move or size with cells" option and OK.

Thanks! Had no idea you could do that... So simple!

From my code I pasted above, do you know why my headers "A" and "D" are not deleting?

Because you are deleting an entire column and as the result the columns shift.

Is there a way to make it work so that only the headers:

"Name Title Age Position ID" stay despite the result of columns shifting?

Pho6,
Please do not reply with quote, just a reply will do.
I am not sure I understand your request. How different from what you now have?

Sorry about that.

Basically, when I run the code it currently gives me a result of:

Name A Title Age Position D ID

Even though the code: "If InStr("Name Title Age Position ID", Cells(9, j).Text)" does not include "A" or "D" in the string. Is this occuring because of the columns shifting? Is there anyway to change it so after the macro runs, it only gives me:

Name Title Age Position ID

Thanks

InStr function match any word with in a text. Age match A and ID match D

I see. Got it now. Thanks!