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

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

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

FYI...Here's my revised version. The only substantive change I made was to
adapt to a range that might have both filtered and hidden rows.

Charley
'====================
Sub ConvertToFilter()
Dim rngData As Range, rngFilter As Range, rngVisFilter As Range
Dim lCol As Long, rngInnerFilter As Range

Set rngData = ThisWorkbook.Names("AllData").RefersToRange

''Define the full column of x's (show) and blanks (hide)
Set rngFilter = ThisWorkbook.Names("datFilter").RefersToRange

''Define the inner range
Set rngInnerFilter = rngFilter.Offset(1,
0).Resize(rngFilter.Rows.Count - 2, 1)

''Get the hidden range, which could be from both filtering and manual
hiding
Set rngVisFilter = rngInnerFilter.SpecialCells(xlVisible)

''If the filter mode is on, then turn it off
If rngData.Worksheet.FilterMode Then rngData.AutoFilter

''Unhide any manually hidden rows
rngData.Rows.Hidden = False

''Erase the inner area of the entire column
rngInnerFilter.ClearContents

''Mark with an x only the data that had been visible
rngVisFilter.Value = "x"

''Expand rngData to include the column with the markers
Set rngData = rngData.Resize(, rngFilter.Column - rngData.Column + 1)

''Apply the filter
rngData.AutoFilter _
Field:=rngData.Columns.Count, _
Criteria1:="x"
End Sub

'===========================

"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