I've used this technique when I import files.

I get the data and put it in row 3. I add headers to row 2. I add autofilters
to that range (A2:X9999). But I put all my subtotals in row 1. (and set rows
to repeat at top to 1:2 and freeze panes so that rows 1 and 2 are always
visible).

I find it much easier and even more useful to have the subtotals at the top. If
I use =subtotal()'s in my formulas, they react to the changes in my
autofiltering. And I don't have to scroll through the worksheet to find those
numbers.

Another approach.

Drop the subtotals completely and use data|pivottable to create a nice summary
table. (Or use both the subtotals at the top and pivottables.)

Dallman Ross wrote:

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]

--

Dave Peterson