How delete rows in named range

[John]

I'm want to delete all rows in a named range table except the first and last.
I tried:
AIRowCount = Range("_AI_Robin_ActionTable").Rows.Count
Range("_AI_Robin_ActionTable").Rows(2, AIRowCount - 1).Delete Shift:=xlUp
but the VB doesn't like the 2 arguments in the Rows function.

How do I select multiple rows?

Better yet, where do I find documentation that will tell me what all the
Excel VB coding functions (or whatever you call those words between the dots)
are, how they work and what their parameters are? (I'd like to become a
better fisherman rather than a better fish moocher.) Is there some way I can
get or use the Excel 2007 VB editor to be a little more helpful producing
correct code?

Thx so much for your help, John

 

[Tim Zych]

Dim rng As Range
Set rng = Range("_AI_Robin_ActionTable")
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 2)
rng.Delete Shift:=xlUp

 

[Tim Zych]

Here is a modification with better validation

Dim rng As Range
Set rng = Range("_AI_Robin_ActionTable")
If rng.Rows.Count > 2 Then
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 2)
rng.Delete Shift:=xlUp
End If

 

[Rick Rothstein]

You can use a With..End With block to eliminate the Range
variable and simplify the code a little bit (IMHO)...

With Range("_AI_Robin_ActionTable")
If .Rows.Count > 2 Then .Offset(1).Resize(.Rows.Count - 2).Delete Shift:=xlUp
End With

 

[Tim Zych]

Well...I like variables for the following reason:

My way sets a reference once, which can be reused anywhere in the module. If
I want to change the range name to something else, I have only one place to
change it. Your way requires repeated references to the range name string,
or a humongous With...End With block which might hold code that should not
be there.

I don't see anything beneficial about your way except extra work later on. I
am also not a fan of If..Then in one line, since I might want to add code
within the evaluation at a later time. Your way I would have to make a
bigger adjustment.

Just my $.02.

--
Tim Zych
http://www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
Free and Pro versions available


You can use a With..End With block to eliminate the Range
variable and simplify the code a little bit (IMHO)...

With Range("_AI_Robin_ActionTable")
If .Rows.Count > 2 Then .Offset(1).Resize(.Rows.Count - 2).Delete
Shift:=xlUp
End With

 

[Rick Rothstein]

Those are fair arguments and I can't fault you for them. Obviously, though, my preferences are different from yours.