In , Dave Peterson
spake thusly:

But you use Set when you want to make an assignment to an object
variable.

Dim Wks as worksheet
set wks = worksheets(...)

Ah! Gotcha, thanks.

=====================
In my sort sample, I did want key1:=.columns(2). This meant that
my column to sort by was column 2 of that range (the dot (.) said
that columns(2) belonged to the previous With statement. It
wasn't an error.

Okay. I'm not yet clear on something about it. I guess it's because
I was converting to your syntax from my

Range(Selection, "A1").Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes

So I'm sorting based on Column 1. Okay, I'll change it to:

With myRng
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

Yes, I just tried it, and that works fine. Sorry for being dense!


=====================
I like typing:

with worksheets("sheet999")
set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol))
end with

But if you like you can qualify each range reference:

set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _
worksheets("sheet999").cells(ilastrow, ilastcol))


Okay, I see what you like about it. I agree that it's cleaner.

==========
I'm not sure what you're refreshing that causes the warning, but
lots of times, you can suppress warnings by:

application.displayalerts = false
'your code that causes the warning message here
application.displayalerts = true

I'll see how that works. I'm painstakingly building this macro
line-by-line, and I'm not quite there yet. I had a macro earlier
that worked, but I've completely changed my sheet around since
then -- and that macro was not at all optimal, anyway.

Another option would be to remove your subtotals, add your rows,
and reinsert the subtotal rows.

Well, it's formatted a certain way and has some complex calculated
fields, not just totals, so I'd rather not use that approach. I
also could temporarily move it to Row 1, then move it back. If I
do, I think I'll want to turn off the display refresh during that
part of the macro, because that will grate on my sensibilities for
some reason I can't quite put into words. I suppose the reason
is that I'm a purist at heart, but I know too little about what
I'm doing with VBA to be able to live up to my high demands
of myself for creating robust and reusable code.

Or insert your new data above the subtotal rows.

The problem is, I don't know how long the new data is until I refresh
that sheet's data source. The source is a CSV file, so I can't
use fancy VBA code to inspect it first, either.

David McRitchie has some notes about not having to adjust
formulas when additional rows are inserted:

http://www.mvps.org/dmcritchie/excel...row.htm#offset

Bookmarked! Thanks again.

Dallman Ross

[Prior stuff deleted]