Delete same columns in every worksheet

[Books4Sale]

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

Thanks,
John

 

[JulieD]

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

 

[Gord Dibben]

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

 

[Debra Dalgleish]

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

 

[Domenic]

Hi Julie,

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

Thanks!

 

[JulieD]

Hi Domenic

columns or rows?

Cheers
JulieD

Hi Julie,

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

Thanks!

 

[Books4Sale]

I never knew about grouping sheets. Thanks!

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

 

[Domenic]

columns or rows?

Columns please. For rows wouldn't I just have to change it to...

sh.Rows(1).Delete

 

[Dave Peterson]

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.

 

[Domenic]

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.

 

[Dave Peterson]

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.

 

[Domenic]

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.

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!

 

[Dave Peterson]

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!

 

[Domenic]

Can you post all of your code?

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

 

[Dave Peterson]

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.

 

[Domenic]

And this worked differently in the Mac version?

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

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.

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!

 

[Dave Peterson]

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!)

 

[Domenic]

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...

 

[Dave Peterson]

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.

 

[Dave Peterson]

And maybe you could post your question (and results) on:

microsoft.public.mac.office.excel

to see if they could duplicate your results.