PDA

View Full Version : Solved: case statement for sheets



satyen
03-22-2010, 08:16 AM
I am writing a CASE statement to delete contents and unhide rows in certain sheets.
Coming up with an error once it completes. “Object Variable or With Block Variable not set”

Can anyone help please?


Sub test()
Dim Sh As Worksheet


For Each Sh In ActiveWorkbook.Worksheets


Select Case Sh.Name
Case "a", "b", "b"

Cells.Select
Selection.Delete Shift:=xlUp
Selection.EntireRow.Hidden = False
ActiveSheet.Next.Select
Debug.Print Sh.Name

End Select

Next Sh
End Sub

Paul_Hossler
03-22-2010, 08:20 AM
I think that "ActiveSheet.Next.Select" is unneeded since you're already in a For loop

Paul

satyen
03-22-2010, 08:27 AM
It only actions the code for the active sheet and not the rest of them mentioned in the case statement.

satyen
03-22-2010, 09:05 AM
so that is why I put ActiveSheet.Next.Select, any other ideas?

mbarron
03-22-2010, 09:14 AM
You need to take out the ActiveSheet.Next.Activate if one of your Case sheets is the last sheet in your book.
Try this version:
Option Explicit

Sub test()
Dim Sh As Worksheet


For Each Sh In ActiveWorkbook.Worksheets


Select Case Sh.Name
Case "a", "b", "b"

sh.Cells.Delete
sh.Cells.EntireRow.Hidden = False
Debug.Print Sh.Name

End Select

Next Sh
End Sub

Bob Phillips
03-22-2010, 09:24 AM
Why would you use


Sheets(Sh.Name). etc.


and not


sh. etc.

satyen
03-23-2010, 05:20 AM
excellent thank you all!