Summary sheet

[Dennis Allen]

Hi. I have a workbook with 400 sheets. Each sheet contains an area for log entries (A11510). 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

 

[Jack Schitt]

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 have a workbook with 400 sheets. Each sheet contains an area for

log entries (A11510). 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.

 

[Dennis Allen]

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?

 

[Jack Schitt]

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 sure:

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 :-(

 

[Jack Schitt]

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.

 

[Dennis Allen]

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?

 

[Dennis Allen]

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?

 

[Jack Schitt]

You could try

rg.EntireRow.Hidden = True

 

[Jack Schitt]

Something like:

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

(found by recording macro)

 

[Dennis Allen]

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.

 

[Jack Schitt]

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.

 

[Dennis Allen]

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.

 

[Jack Schitt]

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.

 

[Jack Schitt]

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.

 

[Dennis Allen]

Found a way that works:


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

 

[Jack Schitt]

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.

 

[Jack Schitt]

Forget that. missed the autofit

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.

Found a way that works:


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