Simple Macro Works on PC But Not Mac--Help!

[Guest]

The following macro loops through rows 5 to 500, and if the cell in Column AG
is dark gray (ie, has a Color Index of 16), hides that row:

Sub HideRows()
BeginRow = 5
EndRow = 500
ChkCol = 33
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Interior.ColorIndex = 16 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

This routine works fine on my PC at home (XL 2003), but on my Mac at work
(XL for MAC 2004) it poops out after hiding just the first gray row it comes
to. Why will this loop through the whole range on my PC but not my Mac?
Doesn't anyone have an alternative?

Please help. This is driving me insane. Thanks!

 

[Jim Cone]

I don't use a Mac, but sometimes I get lucky.
I could not find "poops out" defined anywhere in VBA help.
Is that a Mac term?
Some possibilities...
Try declaring all of your variables including RowCnt as Long.
Is the same workbook used at home and at work?
Maybe Column 33 is not the same in both workbooks.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"PBJ" <[email protected]>
wrote in message
The following macro loops through rows 5 to 500, and if the cell in Column AG
is dark gray (ie, has a Color Index of 16), hides that row:

Sub HideRows()
BeginRow = 5
EndRow = 500
ChkCol = 33
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Interior.ColorIndex = 16 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

This routine works fine on my PC at home (XL 2003), but on my Mac at work
(XL for MAC 2004) it poops out after hiding just the first gray row it comes
to. Why will this loop through the whole range on my PC but not my Mac?
Doesn't anyone have an alternative?

Please help. This is driving me insane. Thanks!

 

[Guest]

Thanks for the reply, but unfortunately the results are negative. The same
workbook is accessed on both PCs and Macs, and column 33 is the same in both.
(I've also verified that the color index for the shaded rows is the same on
both platforms.)

I also just declared the variables, but the macro still doesn't cycle
through all the rows. It hides the first gray row it comes to and then stops,
but without any error messages, etc. For some reason I think the loop is
breaking after the first instance where the IF criterion is true.

Thanks again, though. Let me know if you have any other ideas!

 

[Rick Rothstein \(MVP - VB\)]

I see no reason why your loop shouldn't work either. Not being a Mac person,
I can't test this idea out. To start off with, let me state that I have
absolutely no reasoning behind this suggestion other than it can't hurt to
try it.<g> What happens if you run your loop backwards? Try changing your
For-Next statement to this...

For RowCnt = EndRow To BeginRow Step - 1

and see if it makes a difference.

Rick