Deleting Multiple Columns in Excel via macro

[tmar]

I've got a spreadsheet template where I want the user to be able to click on a button and have it delete a series of columns. Something Columns "A", "F:G", "K:M" etc.

I tried the following code which deleted the first series but not the next. Any help would be appreciated.

Sub btnCleanSheet()
cleanit
End Sub

Sub cleanit()
Columns("A").Delete
Columns("F:G").Delete
End Sub

 

[SkipVought]

Hi,

Think about it!

What happens to columns F & G when you delete ANY column to the LEFT of them?

What does that tell you that you ought to do procedurally?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Instead of deleting the columns, you might just want to hide them. The reason why this might be better is that you won't get into the problem you identifing above, that when you delete a column, all of the others are Shifted to the Right (e.g., if you delete columns F:G the data in cols H is shifted to Col F, Col I to G, etc.) Therefore if you want to Delete colums, you need to do them from the one farther to the Right to the left (e.g., in your example above, Cols K:M then F:G and finally A)

 

[Andrzejek]

If you want the code to delete the columns you mentioned, in Excel select View, click Macro - Record Macro - give it a name or leave Macro1 - OK

Select columns A, hold Cntr key and select columns F:G and then K:M, right-click on any of the selected columns heading and click Delete.

Alt-F11 will get you to VBA editor. In Module1 you will find your code which you can express with just one line of code:

Range("A:D,F:G,K:M").Delete Shift:=xlToLeft

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.