Delete same columns in every worksheet

  • Thread starter Thread starter Books4Sale
  • Start date Start date
B

Books4Sale

Is there a way to delete the same columns (A & B) in every worksheet in the
workbook?

Thanks,
John
 
Hi John

- this will work unless there are bits of a pivot table or merged cells in
columns A & B

Sub deletecols()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Columns(1).Delete
sh.Columns(1).Delete
Next

End Sub

Cheers
JulieD
 
John

Select first sheet then right-click on sheet tab and "select all sheets" to
group the sheets.

Delete columns A and B on active sheet. All sheets will have A and B deleted.

DO NOT FORGET to right-click and "ungroup sheets" when finished.

What you do to one gets done to all in the "group"

Gord Dibben Excel MVP
 
You can group the sheets, then delete the columns --

Right-click on any sheet tab
Choose Select All Sheets
Delete columns A and B
Right-click on any sheet tab
Choose Ungroup Sheets
 
Hi Julie,

How can you change that to have columns 10 to 20 deleted for every
worksheet?

Thanks!
 
I never knew about grouping sheets. Thanks!

Gord Dibben said:
John

Select first sheet then right-click on sheet tab and "select all sheets" to
group the sheets.

Delete columns A and B on active sheet. All sheets will have A and B deleted.

DO NOT FORGET to right-click and "ungroup sheets" when finished.

What you do to one gets done to all in the "group"

Gord Dibben Excel MVP
Click to expand...
 
One way:

Option Explicit
Sub deletecols()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Range("a1:c1,m1:q1").EntireColumn.Delete
Next

End Sub

You could do this if you wanted to get 20 columns starting with column C:

Option Explicit
Sub deletecols()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Range("c1").resize(1,20).EntireColumn.Delete
Next

End Sub

It starts with C1, resizes it to 1 row by 20 columns and then deletes the entire
column of that resized range.
 
Thanks Dave!

The second macro works beautifully. The first macro, however, deletes
columns starting from Column S. The problem may be that I'm using a Mac
version of Excel.
 
Try that first one again against a test workbook. I'd be very surprised if
there was a difference between the windows version and the mac version.
 
Dave Peterson <[email protected]> said:
Try that first one again against a test workbook. I'd be very surprised if
there was a difference between the windows version and the mac version.
Click to expand...

I tried it again and got the same results. With sample data entered
from Column A to Column Z, I got the following results:

Range("a1:c1,m1:q1") ---> Column S to Column Z deleted

Range("a1:c1") ---> Columns X, Y, and Z deleted

Range("a1") ---> Column Z deleted

Very strange indeed!
 
Can you post all of your code?
I tried it again and got the same results. With sample data entered
from Column A to Column Z, I got the following results:

Range("a1:c1,m1:q1") ---> Column S to Column Z deleted

Range("a1:c1") ---> Columns X, Y, and Z deleted

Range("a1") ---> Column Z deleted

Very strange indeed!
Click to expand...
 
Dave Peterson <[email protected]> said:
Can you post all of your code?
Click to expand...

I'm using the following code (ALT+F11 > Insert > Module):

Option Explicit
Sub deletecols()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Range("a1:c1,m1:q1").EntireColumn.Delete
Next

End Sub
 
And this worked differently in the Mac version?

For me it deleted A:C and M:Q.

Wow.

Maybe one more test???

Option Explicit
Sub deletecols()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Activate
sh.Range("a1:c1,m1:q1").EntireColumn.Select
Next

End Sub

I just can't believe that something this plain works differently on a mac. That
sounds scary to me.
 
Dave Peterson <[email protected]> said:
And this worked differently in the Mac version?
Click to expand...

Yep! Believe it or not. :-)
Maybe one more test???

Option Explicit
Sub deletecols()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Activate
sh.Range("a1:c1,m1:q1").EntireColumn.Select
Next

End Sub
Click to expand...

This time it highlights the relevant columns for each of the sheets but
that's it. It doesn't delete the columns.
I just can't believe that something this plain works differently on a mac.
That sounds scary to me.
Click to expand...

I'm surprised too. I usually tend to shy away from VBA code unless it's
specifically written for the Mac. But this time because it was
something plain I thought I wouldn't have a problem. Oh well...

Thanks Dave!
 
One more followup.

Do you have merged cells in your data. In excel for windows, differerent
versions treated merged cells differently.

In xl97 (IIRC), if you had cells that were merged (say A1:E1), then when you
deleted a column from that range say C1, then all of A1:E1 were deleted.

xl2002 is much more, er, forgiving. It'll just delete that one column.

(I'm still having a difficult time believing that this doesn't work on a Mac.
But my Mac experience was a 15 years ago. It took an hour for something that
should have taken seconds!)
 
Hi Dave,

I have no merged cells in my data, whatsoever. I used a new
file/workbook, filled in Columns A to Z on each of the three sheets,
inserted the code, and ran the macro. And you know the rest of the
story... :-)
 
This one hurts my head!

I just can't believe that this code doesn't work the same way on a Mac.

(I'm not calling you a liar, well not outloud <vbg>, but I don't get it.)

Thanks for sticking with me.
 
And maybe you could post your question (and results) on:

microsoft.public.mac.office.excel

to see if they could duplicate your results.
 
Back
Top