Delete rows in multiple sheets without loop?

[MTT727]

I have a workbook into which I occastionally import new data sets, eac
in its own new worksheet. I need to delete the third row of each ne
worksheet (but not any not-new worksheet).

I'm trying to avoid using a loop to do this (as an exercise). So I'
wondering if I can define a new worksheets object called wsNewSheets a
a collection containing just the new worksheets and then do somethin
like

wsNewSheets.Rows(3).Delete

Would that work? I can't figure out how to 'populate' wsNewSheets.

Thanks,
Ma

 

[Jim Rech]

As far as I know you cannot create a Worksheets object which represents a
subset of all the worksheets in a workbook. 'Worksheets' is all the
worksheets in a workbook.

You can create an array of worksheets, and that can be a convenient way to
do some operations (like printing and deleting). But as to deleting rows, I
think that has to be done on each sheet individually.

Sub a()
Dim SheetArray As Variant
Set SheetArray = Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
MsgBox SheetArray(1).Name & " " & SheetArray(2).Name & " " &
SheetArray(3).Name
SheetArray(1).Rows(3).Delete ''This works
SheetArray.Rows(3).Delete ''This fails
End Sub


--
Jim
message |
| I have a workbook into which I occastionally import new data sets, each
| in its own new worksheet. I need to delete the third row of each new
| worksheet (but not any not-new worksheet).
|
| I'm trying to avoid using a loop to do this (as an exercise). So I'm
| wondering if I can define a new worksheets object called wsNewSheets as
| a collection containing just the new worksheets and then do something
| like
|
| wsNewSheets.Rows(3).Delete
|
| Would that work? I can't figure out how to 'populate' wsNewSheets.
|
| Thanks,
| Mac
|
|
| --
| MTT727
| ------------------------------------------------------------------------
| MTT727's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25585
| View this thread: http://www.excelforum.com/showthread.php?threadid=390067
|

 

[Tom Ogilvy]

Just to add to Jim's excellent advice. You can sometimes use selection in
conjunction with grouped sheets to do this:

Sub tester1()
Dim sh As Worksheet
Set sh = ActiveSheet
Worksheets(Array("Sheet1", "Sheet3", "Sheet5")).Select
Rows(3).Select
Selection.Delete
sh.Select
End Sub