Test VBA Code for Mac Compatability

[Domenic]

I was given the following code by Dave Peterson which is suppose to
delete the same columns on every sheet within a workbook.

Unfortunately, when I run the macro it doesn't behave as it should.
Dave feels that the code should work on a Mac and doesn't understand why
it doesn't.

So we're wondering if anyone else has the same problem, or whether the
code runs as it should on other Macs.

Here's the code I'm using...

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

When I run it, it deletes columns starting from Column S (I have data
from Columns A to Column Z on three sheets), not the columns stated in
the code (Columns A to C and Columns M to Q).

And when I change the range to the following, I get the corresponding
results:

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

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

Anyone else get the same results?

 

[JE McGimpsey]

I was given the following code by Dave Peterson which is suppose to
delete the same columns on every sheet within a workbook.

Unfortunately, when I run the macro it doesn't behave as it should.
Dave feels that the code should work on a Mac and doesn't understand why
it doesn't.

Note: It's extraordinarily hard to troubleshoot if you don't say which
version of MacXL you're using!

It works fine for me XL04 - deleting the proper columns.

When I run it, it deletes columns starting from Column S (I have data
from Columns A to Column Z on three sheets), not the columns stated in
the code (Columns A to C and Columns M to Q).

And when I change the range to the following, I get the corresponding
results:

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

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

Anyone else get the same results?

I can't reproduce any of these.

 

[Domenic]

Note: It's extraordinarily hard to troubleshoot if you don't say which
version of MacXL you're using!

Sorry JE! I'm using Excel X (10.1.5)

It works fine for me XL04 - deleting the proper columns.

Hmmm....interesting. Thanks for checking it out for me!

 

[JE McGimpsey]

Note: It's extraordinarily hard to troubleshoot if you don't say which
version of MacXL you're using!

Sorry JE! I'm using Excel X (10.1.5)

It works fine for me XL04 - deleting the proper columns.

Hmmm....interesting. Thanks for checking it out for me![/QUOTE]

Just tested it in XLv.X - works correctly for me.

 

[Domenic]

Just tested it in XLv.X - works correctly for me.

Well, I don't understand why it won't work for me. Maybe I just don't
know the magic words.

I just tried it again, just to be sure, and got the same incorrect
results. I pasted the code in the regular module --> ALT+F11 > Insert >
Module.

Do you think it could be some sort of bug on my part?

 

[Bob Greenblatt]

I was given the following code by Dave Peterson which is suppose to
delete the same columns on every sheet within a workbook.

Unfortunately, when I run the macro it doesn't behave as it should.
Dave feels that the code should work on a Mac and doesn't understand why
it doesn't.

So we're wondering if anyone else has the same problem, or whether the
code runs as it should on other Macs.

Here's the code I'm using...

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

When I run it, it deletes columns starting from Column S (I have data
from Columns A to Column Z on three sheets), not the columns stated in
the code (Columns A to C and Columns M to Q).

And when I change the range to the following, I get the corresponding
results:

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

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

Anyone else get the same results?

I noticed you are in Canada. By any chance re you using a French system? Are
the range references the same, or perhaps ambiguous? Try referencing the
range as "a:c,m:q".

Keep us posted with the results.

 

[Domenic]

I noticed you are in Canada. By any chance re you using a French system?

No, not at all.

Are the range references the same, or perhaps ambiguous?

I'm not sure what you mean? I'm using the references exactly as stated
in the code.

Try referencing the range as "a:c,m:q".

Just tried it, and I get the same results. Interesting!

 

[JE McGimpsey]

Well, I don't understand why it won't work for me. Maybe I just don't
know the magic words.

I just tried it again, just to be sure, and got the same incorrect
results. I pasted the code in the regular module --> ALT+F11 > Insert >
Module.

Do you think it could be some sort of bug on my part?

While it's possible, it seems unlikely to be a bug - it's too specific.
However, I don't have a clue what could cause this. Does this happen on
one file only or with a new file?

Do you have any other macros (including event macros) running?

You could try the techniques found at

http://www.mcgimpsey.com/macoffice/excel/troubleshooting.html

 

[Domenic]

While it's possible, it seems unlikely to be a bug - it's too specific.
However, I don't have a clue what could cause this. Does this happen on
one file only or with a new file?

I use a new file every time I test it.

Do you have any other macros (including event macros) running?

I have an add-in in the Excel Start-Up folder. I just removed it and
tried running the code again, but got the same results. I also tried
removing the Analysis ToolPak add-in, but nothing doing.

You could try the techniques found at

http://www.mcgimpsey.com/macoffice/excel/troubleshooting.html

Thanks JE. I'll have a look at them and let you know how it works out.

 

[Bernard Rey]

Domenic wrote :

No, not at all.

Just for the record : the macro works fine on the French versions of Excel X
& 2004 too. But I know it won't bring much help :-/

 

[Domenic]

Just for the record : the macro works fine on the French versions of Excel X
& 2004 too. But I know it won't bring much help :-/

Actually it helps confirm that the macro is indeed fine and that there
must be something wrong on my end. So I appreciate the input.

Merci beaucoup, Bernard! That's about the extent of my French.

 

[Jim Gordon MVP]

Hi Domenic,

The proper columns were deleted for me in XL2004. I can't think of an
Excel setting that would cause otherwise.

Try deleting the Excel plist file and see if that does anything to fix
things for you.

-Jim

 

[Domenic]

Hi Jim,

I just tried deleting the Excel plist file and I'm still getting the
same results. I noticed there was a VBA preference file, so I thought
I'd try deleting that too, but that didn't help either.

Tomorrow morning I'll visit JE's website where there's a section on
troubleshooting. Hopefully I'll be able to get to the bottom of this.

Thanks for trying to help!

 

[JE McGimpsey]

I just tried deleting the Excel plist file and I'm still getting the
same results. I noticed there was a VBA preference file, so I thought
I'd try deleting that too, but that didn't help either.

Make sure when you delete these files that XL (or in the case of VBA
Prefs, *all* Office apps) is closed. Otherwise the files will be written
from memory, which may still be corrupted.

 

[Domenic]

Make sure when you delete these files that XL (or in the case of VBA
Prefs, *all* Office apps) is closed. Otherwise the files will be written
from memory, which may still be corrupted.

Thanks for pointing this out to me. When I deleted the preferences, I
still had other Office applications opened. So I retried it with all
applications closed, but still nothing. So I'm now off to visit your
website and do some troubleshooting...

 

[Domenic]

Thanks for pointing this out to me. When I deleted the preferences, I
still had other Office applications opened. So I retried it with all
applications closed, but still nothing. So I'm now off to visit your
website and do some troubleshooting...

JE, after following the steps recommended on your website for
troubleshooting, the macro still doesn't work properly.

It looks like I've tried everything, including removing and
re-installing Office X.

I tried running the macro right after I re-installed it and also after
updating to the latest versions, but nothing helped.

From time to time, I tried out some of the codes that had been offered
by others and found that they either didn't work or didn't work properly.

I always assumed that it was due to the cross-compatibility issue. But
now I'm wondering if at least some of the time the problem had to do
with my copy of Excel/VBA application.

So right now I'm not sure where I go from here. But I'd like to thank
you for your help, and thank everyone else who helped by weighing in
with their results.

 

[Bernard Rey]

Domenic wrote :

JE, after following the steps recommended on your website for
troubleshooting, the macro still doesn't work properly.

It looks like I've tried everything, including removing and
re-installing Office X.

I tried running the macro right after I re-installed it and also after
updating to the latest versions, but nothing helped.

From time to time, I tried out some of the codes that had been offered
by others and found that they either didn't work or didn't work properly.

I always assumed that it was due to the cross-compatibility issue. But
now I'm wondering if at least some of the time the problem had to do
with my copy of Excel/VBA application.

So right now I'm not sure where I go from here. But I'd like to thank
you for your help, and thank everyone else who helped by weighing in
with their results.

If you feel it could help, you can send me a copy of a "non working"
workbook. Just to see if it sticks somewhere in the workbook or if it's
really a your-machine-only feature :-(

 

[Domenic]

If you feel it could help, you can send me a copy of a "non working"
workbook. Just to see if it sticks somewhere in the workbook or if it's
really a your-machine-only feature :-(

Oh yes, I would really appreciate it!

I'll open a new workbook, enter some fake data, and insert the macro in
question.

I'll send it in the next few minutes.

 

[Bernard Rey]

Domenic wrote :

Oh yes, I would really appreciate it!

I'll open a new workbook, enter some fake data, and insert the macro in
question.

I'll send it in the next few minutes.

OK, I think I got it. In fact, I think the problem is your sheet is filled
with "a" characters. So how can you know which columns are deleted?

If you type "a" in cell A1; "b" in cell B1; "c" in cell C1 and so on. That
way, when you'll run the macro, you'll figure out which cells have been
deleted. And I believe this time things are going to be "normal"...

In the workbook you sent over, deleting the indicated eight columns did
delete them, but the effect of it is that the remaining columns get shifted
to the left. And the eight last columns seem to have been cleared. In fact,
eight blank columns have been shifted over there.

Maybe what you would like to see is a set of blank cells in the selected
columns. But then, it's a different request, you should not "delete" the
columns, but "clear" its contents. So you'd have to modify the macro:

---------------------------------------------------------

Option Explicit
Sub deletecols()
Dim sh As Worksheet

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

End Sub

---------------------------------------------------------

This way, you'll have the cells content cleared, but the columns will remain
where they are.

Does that make sense, or am I wrong?

 

[Domenic]

Bernard,

You're 100% correct!!! I feel like such a fool.

Here I am thinking that it would clear the contents (which is what I was
looking for) when in fact the macro is designed to delete it. And as
you've already said the columns get shifted left.

And to think of all of the time I spent on this, including re-installing
Office X. And of course all the time spent by everyone else here trying
to help me. Oh boy... I owe everyone a debt of gratitude.

Well I can certainly rest easier now that I know there's nothing wrong
with my Excel/VBA application. It's definitely a h-u-g-e relief!!!

Again, thank you Bernard, and thanks to everyone else who tried to help!
I really appreciate it!