Thanks, Tom! I forgot about SpecialCells(xlVisible)!

All the best,

Charley


"Tom Ogilvy" wrote in message
...
Sub ConvertToFilter()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim col As Long
Set rng = Range("A1").CurrentRegion
col = rng.Columns(rng.Columns.Count).Column
Set rng1 = rng
Set rng1 = rng1.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
Set rng2 = Intersect(rng1.EntireRow, Cells(1, col + 1).EntireColumn)
Set rng3 = rng2.SpecialCells(xlVisible)
rng2.Value = "Hide"
rng3.Value = "Show"
Cells(1, col + 1).Value = "Header"
Cells.Rows.Hidden = False
Set rng = rng.Resize(, rng.Columns.Count + 1)
rng.AutoFilter Field:=rng.Columns.Count, _
Criteria1:="Show"
End Sub

should impose an autofilter on your data - subtotal will work with this,
but
not DSUM. However, subtotal should do what you want.

You could modify it to write a criteria range

header
Show

and set an advanced filter or use the Dfunctions.

--
Regards,
Tom Ogilvy



"Charley Kyd" wrote in message
...
I've got a 50,000-row Excel database with various controls that hide
rows
for various reasons. Now I want to summarize the non-hidden rows in
various
ways. Unfortunately, SUBTOTAL, DSUM, and similar functions rely directly
or
indirectly on a Criteria range.

Logically, I have two choices. I could write custom SUM, COUNT, and
other
such functions, which would apply only to the unhidden rows.
Alternatively,
I could mark each row somehow so that a simple Criteria could recognize
the
unhidden rows, filter the data, and allow me to use SUBTOTAL, DSUM, and
the
like.

Any of these alternatives would be possible, of course. But they all
require
many seconds to perform. This creates a very sluggish user interface.

Can anyone suggest a speedy way to transform a database with hidden rows
into a filtered database with the same rows hidden?

Thanks.

Charley