error - select method failed - (columns.select)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PBcorn - 19 May 2008 10:20 GMT

Getting a 1004 error - select method of range class failed - with the
following code:

Sub unhide()

Dim sh As Object

For Each sh In ActiveWorkbook.Worksheets

sh.Visible = True

sh.Columns.Select

Selection.EntireColumn.Hidden = False

Next sh

End Sub

the code is in a module. The macro works for the first sheet of the
three-sheet workbook then bombs on the second sheet

Thanks in advance

PB

Reply to this Message

Chip Pearson - 19 May 2008 10:29 GMT

When you use a For Each loop with the Worksheets collection, the individual
sheets are *not* automatically made active as the loop spins. Also, you
cannot Select a range that is not on the active sheet. Together, these facts
cause the line

sh.Columns.Select

to fail. The sh variable refers to a worksheet that is not active and then
Select attempts to select a range on the sh worksheet. Use

sh.Select
sh.Columns.Select

Of course, it isn't necessary to Select anything, so you can use just

sh.Columns.EntireColumn.Hidden = False

within the loop.

Signature

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

> Getting a 1004 error - select method of range class failed - with the
> following code:
[quoted text clipped - 21 lines]
>
> PB

Reply to this Message

PBcorn - 19 May 2008 10:49 GMT

thanks. as you can tell I am in the early stages of learning VB.

> When you use a For Each loop with the Worksheets collection, the individual
> sheets are *not* automatically made active as the loop spins. Also, you
[quoted text clipped - 40 lines]
> >
> > PB

Reply to this Message

Norman Jones - 19 May 2008 10:31 GMT

Hi P,

Your error is encountered because you
cannot make selections on an inactive
sheet.

Try the following version:

'===========>>
Sub unhide()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
sh.Columns.Hidden = False
Next sh
End Sub
'<<===========

---
Regards.
Norman

> Getting a 1004 error - select method of range class failed - with the
> following code:
[quoted text clipped - 21 lines]
>
> PB

Reply to this Message