Summary sheet
 

Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11:D510). Column A cells has either Y or N,
Column B cells has dates, etc. I have a summary sheet with 400 rows, one row for each sheet.

I want to create another summary sheet, but only showing a row if the corresponding sheet has at least one row with a Y in column A.
Can it be done? Can I do it in VBA? If so, how? Any advice is appreciated...Dennis

Summary sheet
 

It is probably not quite what you are looking for, but a "simple" solution
might be to reserve a column in your existing summary sheet to return on
each row either TRUE or FALSE depending on whether the count of "Y" in
column A of the respective sheet dedictated to that row in the summary is
greater than 0. Then autofilter on that column.

"Dennis Allen" wrote in message
...
Hi. I have a workbook with 400 sheets. Each sheet contains an area for
log entries (A11:D510). Column A cells has either Y or N,
Column B cells has dates, etc. I have a summary sheet with 400 rows, one
row for each sheet.

I want to create another summary sheet, but only showing a row if the
corresponding sheet has at least one row with a Y in column A.
Can it be done? Can I do it in VBA? If so, how? Any advice is
appreciated...Dennis

Summary sheet
 

"Jack Schitt" wrote in message ...
It is probably not quite what you are looking for, but a "simple" solution
might be to reserve a column in your existing summary sheet to return on
each row either TRUE or FALSE depending on whether the count of "Y" in
column A of the respective sheet dedictated to that row in the summary is
greater than 0. Then autofilter on that column.


Hi. I tried autofilter and it works, but when someone adds a "Y" entry it does not show up on the summary sheet. To get it to show
you have to rerun autofilter. Isn't there something we could use that would be more dynamic?

Summary sheet
 

"Dennis Allen" wrote in message
...

"Jack Schitt" wrote in message
...
It is probably not quite what you are looking for, but a "simple"
solution
might be to reserve a column in your existing summary sheet to return on
each row either TRUE or FALSE depending on whether the count of "Y" in
column A of the respective sheet dedictated to that row in the summary is
greater than 0. Then autofilter on that column.


Hi. I tried autofilter and it works, but when someone adds a "Y" entry it
does not show up on the summary sheet. To get it to show you have to
rerun autofilter. Isn't there something we could use that would be more
dynamic?


I expect that someone else will be able to produce something better. A
kludgy, less than optimal answer would be to use conditional formatting in
the summary sheet that colour-codes the entire row depending on whether TRUE
or FALSE appears in the "count of Y" column in the summary sheet. This
doesn't hide the row, unfortunately, but it gets you nearly there.

It will be possible by way of VBA, I am su

In the code behind ThisWorkbook module create a routine:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

then test whether Sh is one of the 400 Sheets, whether Target is in Column
A, whether Y has been entered and then set the rowheight of the
corresponding row in the summary sheet to zero.

Or even, use that routine to refresh the autofilter on the summary sheet,
without even bothering to test Sh and Target.

Unless someone else comes up with an improvement I think I would probably
live with manually refreshing the autofilter, possibly combined with
conditional formatting the row colour so that at least it is obvious to
refresh.

Sorry, but I am really only into the simple stuff :-(

--
Return email address is not as DEEP as it appears

Summary sheet
 

One other possible VBA approach:

Again in the ThisWorkbook module code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Then test whether Sh is the summary sheet. If not, then exit sub. If it
is, then refresh the autofilter.

That sounds workable with less overhead than the other VBA methods I came up
with earlier.


--
Return email address is not as DEEP as it appears
"Jack Schitt" wrote in message
...
"Dennis Allen" wrote in message
...

"Jack Schitt" wrote in message
...
It is probably not quite what you are looking for, but a "simple"
solution
might be to reserve a column in your existing summary sheet to return on
each row either TRUE or FALSE depending on whether the count of "Y" in
column A of the respective sheet dedictated to that row in the summary
is
greater than 0. Then autofilter on that column.


Hi. I tried autofilter and it works, but when someone adds a "Y" entry
it does not show up on the summary sheet. To get it to show you have to
rerun autofilter. Isn't there something we could use that would be more
dynamic?


I expect that someone else will be able to produce something better. A
kludgy, less than optimal answer would be to use conditional formatting in
the summary sheet that colour-codes the entire row depending on whether
TRUE or FALSE appears in the "count of Y" column in the summary sheet.
This doesn't hide the row, unfortunately, but it gets you nearly there.

It will be possible by way of VBA, I am su

In the code behind ThisWorkbook module create a routine:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

then test whether Sh is one of the 400 Sheets, whether Target is in Column
A, whether Y has been entered and then set the rowheight of the
corresponding row in the summary sheet to zero.

Or even, use that routine to refresh the autofilter on the summary sheet,
without even bothering to test Sh and Target.

Unless someone else comes up with an improvement I think I would probably
live with manually refreshing the autofilter, possibly combined with
conditional formatting the row colour so that at least it is obvious to
refresh.

Sorry, but I am really only into the simple stuff :-(

--
Return email address is not as DEEP as it appears

Summary sheet
 

I like the rowheight idea. Was thinking maybe add it into a VBA function of column A. Will look into it. Oh, how would one
refrash autofilter in a VBA function?

"Jack Schitt" wrote in message ...
"Dennis Allen" wrote in message ...

"Jack Schitt" wrote in message ...
It is probably not quite what you are looking for, but a "simple" solution
might be to reserve a column in your existing summary sheet to return on
each row either TRUE or FALSE depending on whether the count of "Y" in
column A of the respective sheet dedictated to that row in the summary is
greater than 0. Then autofilter on that column.


Hi. I tried autofilter and it works, but when someone adds a "Y" entry it does not show up on the summary sheet. To get it to
show you have to rerun autofilter. Isn't there something we could use that would be more dynamic?


I expect that someone else will be able to produce something better. A kludgy, less than optimal answer would be to use
conditional formatting in the summary sheet that colour-codes the entire row depending on whether TRUE or FALSE appears in the
"count of Y" column in the summary sheet. This doesn't hide the row, unfortunately, but it gets you nearly there.

It will be possible by way of VBA, I am su

In the code behind ThisWorkbook module create a routine:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

then test whether Sh is one of the 400 Sheets, whether Target is in Column A, whether Y has been entered and then set the
rowheight of the corresponding row in the summary sheet to zero.

Or even, use that routine to refresh the autofilter on the summary sheet, without even bothering to test Sh and Target.

Unless someone else comes up with an improvement I think I would probably live with manually refreshing the autofilter, possibly
combined with conditional formatting the row colour so that at least it is obvious to refresh.

Sorry, but I am really only into the simple stuff :-(

--
Return email address is not as DEEP as it appears

Summary sheet
 

I've created a column with a function that checks for the "Y" and does rg.EntireRow.RowHeight = 0. Doesn't work. Is RowHeight a
read-only property? Is there another way for a VBA function to hide a row?

Summary sheet
 

You could try

rg.EntireRow.Hidden = True

--
Return email address is not as DEEP as it appears
"Dennis Allen" wrote in message
...
I've created a column with a function that checks for the "Y" and does
rg.EntireRow.RowHeight = 0. Doesn't work. Is RowHeight a read-only
property? Is there another way for a VBA function to hide a row?

Summary sheet
 

Something like:

rDataRange.AutoFilter Field:=2, Criteria1:="Y"

(found by recording macro)

--
Return email address is not as DEEP as it appears
"Dennis Allen" wrote in message
...
I like the rowheight idea. Was thinking maybe add it into a VBA function
of column A. Will look into it. Oh, how would one refrash autofilter in a
VBA function?

Summary sheet
 

"Jack Schitt" wrote in message ...
You could try

rg.EntireRow.Hidden = True

Tried it, couldn't get it to work. Probably read-only. If you want to see a stripped down version, download
http://www.dennisallen.com/temp.xls Agaim, we need another sheet like SUMMARY, but only showing rows that show "Y" in the order of
order column.

Summary sheet
 

"Dennis Allen" wrote in message
...
"Jack Schitt" wrote in message
...
You could try

rg.EntireRow.Hidden = True

Tried it, couldn't get it to work. Probably read-only. If you want to
see a stripped down version, download
http://www.dennisallen.com/temp.xls Agaim, we need another sheet like
SUMMARY, but only showing rows that show "Y" in the order of
order column.


Not sure where your problem is. I downloaded it. I inserted into a new
module (Module2) the following little test:

Sub Test_Hide_Row()
Dim rRow As Range
With ThisWorkbook.Worksheets("Summary")
Set rRow = .Range("5:5")
rRow.EntireRow.Hidden = True
End With 'ThisWorkbook.Worksheets("Summary")
End Sub 'Test_Hide_Row()

It compiled OK, it ran OK, and row 5 in the Summary sheet was hidden as a
result.

Summary sheet
 

I don't what I'm doing wrong either, so I uploaded another copy of http://www.dennisallen.com/temp.xls

In the "Outof Order" sheet, in the visible out of order column I call OutofOrder(). There should be only one row showing.

Summary sheet
 

"Dennis Allen" wrote in message
...
I don't what I'm doing wrong either, so I uploaded another copy of
http://www.dennisallen.com/temp.xls
In the "Outof Order" sheet, in the visible out of order column I call
OutofOrder(). There should be only one row showing.


Hum. I think I see the problem. You have entered the code to hide a row in
a UDF that is called from a cell within the row that is to be hidden. I
would not expect that to work. I think that the hiding of the row will have
to be achieved through a macro rather than a UDF, and an event-driven one at
that. It is late and I have to sleep, but I will think about it again.

--
Return email address is not as DEEP as it appears

Summary sheet
 

One last go before shuteye. I don't reckon that this is the optimal
solution but it was just a quick stab at it and at least it seems to work.

Create a named range that is local to the worksheet "OutOfOrder", thus:

OutOfOrder!MyRange
refers to
=OFFSET(OutOfOrder!$Q$3,1,0,COUNTA($Q:$Q)-1,1)

Then in the class module behind the worksheet OutofOrder (ie not in a
general module), enter the following macro:

Private Sub Worksheet_Activate()
Dim rCell As Range
For Each rCell In Range("MyRange").Cells
If rCell.Value = "Y" Then
rCell.EntireRow.Hidden = False
Else
rCell.EntireRow.Hidden = True
End If
Next rCell
End Sub

You will have to de-activate the worksheet OutofOrder and then re-activate
it for it to run the first time, but thereafter it will run whenever you
activate that worksheet.

--
Return email address is not as DEEP as it appears

Summary sheet
 

Found a way that works:


Private Sub Worksheet_Activate()
Dim c As Range
Application.ScreenUpdating = False
Cells.Rows.AutoFit
For Each c In [p4:p403]
If Not c.Text = "y" Then c.RowHeight = 0
Next c
Application.ScreenUpdating = True
End Sub

Summary sheet
 

Yup that is not far removed from the other method that I posted last thing
last night.
But I suspect that you may prefer my method for one reason:
Your code as it stands will not reset the rowheight to its original state if
a "Y" is subsequently removed from a test cell. Your code could easily be
amended to correct that, of course.

"Dennis Allen" wrote in message
...
Found a way that works:


Private Sub Worksheet_Activate()
Dim c As Range
Application.ScreenUpdating = False
Cells.Rows.AutoFit
For Each c In [p4:p403]
If Not c.Text = "y" Then c.RowHeight = 0
Next c
Application.ScreenUpdating = True
End Sub

Summary sheet
 

Forget that. missed the autofit

"Jack Schitt" wrote in message
...
Yup that is not far removed from the other method that I posted last thing
last night.
But I suspect that you may prefer my method for one reason:
Your code as it stands will not reset the rowheight to its original state
if
a "Y" is subsequently removed from a test cell. Your code could easily be
amended to correct that, of course.

"Dennis Allen" wrote in message
...
Found a way that works:


Private Sub Worksheet_Activate()
Dim c As Range
Application.ScreenUpdating = False
Cells.Rows.AutoFit
For Each c In [p4:p403]
If Not c.Text = "y" Then c.RowHeight = 0
Next c
Application.ScreenUpdating = True
End Sub