Hello everyone ... I'm a real newbie. So please take nothing for granted.
Working in Excell 2007 (Wanting 97-2003 Compatible mode).

What I want to do is based on a cell (containing a formula) within each
group of lines and that is to hide that specific group of lines if the
formula equates to TRUE. Also, I would like to un-hide all rows afterwards.
These functions could be linked to two button controls (on the same
worksheet).

Worksheet name = Exhibit1
There is 142 groups of 4 lines (consisting of 20 columns).
The top left cell of each group (A?) is a cell with a formula resulting in
TRUE (=0) or FALSE (<0) [based on one cell located in the 3rd line eg:
"=IF(R28=0,TRUE,FALSE)"]

Not sure where to start or where to put the code.

Please help ... any suggestions or solutions would be very much appreciated.
Thanks,

Hi Skymann,

Need just a little more information. What is the cell address of the first
cell containing True or False. Is it A1 or do you have column headers and it
is A2?

Regards,

OssieMac

"Skymann" wrote:

Hello everyone ... I'm a real newbie. So please take nothing for granted.
Working in Excell 2007 (Wanting 97-2003 Compatible mode).

What I want to do is based on a cell (containing a formula) within each
group of lines and that is to hide that specific group of lines if the
formula equates to TRUE. Also, I would like to un-hide all rows afterwards.
These functions could be linked to two button controls (on the same
worksheet).

Worksheet name = Exhibit1
There is 142 groups of 4 lines (consisting of 20 columns).
The top left cell of each group (A?) is a cell with a formula resulting in
TRUE (=0) or FALSE (<0) [based on one cell located in the 3rd line eg:
"=IF(R28=0,TRUE,FALSE)"]

Not sure where to start or where to put the code.

Please help ... any suggestions or solutions would be very much appreciated.
Thanks,

Hello OssieMac,

The first Cell containing True or False is A18.

Thanks, Skymann.

"OssieMac" wrote:

Hi Skymann,

Need just a little more information. What is the cell address of the first
cell containing True or False. Is it A1 or do you have column headers and it
is A2?

Regards,

OssieMac

"Skymann" wrote:

Hello everyone ... I'm a real newbie. So please take nothing for granted.
Working in Excell 2007 (Wanting 97-2003 Compatible mode).

What I want to do is based on a cell (containing a formula) within each
group of lines and that is to hide that specific group of lines if the
formula equates to TRUE. Also, I would like to un-hide all rows afterwards.
These functions could be linked to two button controls (on the same
worksheet).

Worksheet name = Exhibit1
There is 142 groups of 4 lines (consisting of 20 columns).
The top left cell of each group (A?) is a cell with a formula resulting in
TRUE (=0) or FALSE (<0) [based on one cell located in the 3rd line eg:
"=IF(R28=0,TRUE,FALSE)"]

Not sure where to start or where to put the code.

Please help ... any suggestions or solutions would be very much appreciated.
Thanks,

Hi

This should do the trick. Starts in Cell A18, checks the length of
Col A and hides the rows with TRUE in them. The Unhide procedure
unhides everything.

Hope this helps.

Marcus

'Procecdure to hide.

Sub Hide()
Dim Z As Long
Dim x As Range

Z = Range("A" & Rows.Count).End(xlUp).Row
Set x = Range("A2:A" & Z)

Application.ScreenUpdating = False
For Each Cell In Range("A18:A" & Z)
Cell.EntireRow.Hidden = Cell.Value = True

Next Cell

End Sub


'Procecdure to unhide.
Sub UnHide()
Dim Z As Long
Dim x As Range

Z = Range("A" & Rows.Count).End(xlUp).Row
Set x = Range("A2:A" & Z)

Application.ScreenUpdating = False
For Each Cell In Range("A18:A" & Z)
Cell.EntireRow.Hidden = False
Next Cell

End Sub

Hi again Skymann,

I have had this reply ready for hours and was waiting on the info on what
cell to commence and I only just got the notification re your reply otherwise
you could have had it earlier. The reply by Marcus appears to only hide one
row where the True exists. I understood that you wanted to hide a group of 4
rows so that is what my macro does so Ill post it anyway.

You also indicated that you are a Newbie and didnt know where to start or
where to put the code which I interpreted as you requiring instructions so
the following guidelines for installing the macro might be overkill but I
think that is better than you having to wait on another answer if you are
unable to complete the task.

First thing that you need to do is ensure that you have macros enabled.
To do this in xl2007:-

Click on Microsoft button (Large button top left of screen)
Select Excel Options (Towards bottom right of dialog box)
Select Trust Centre (Left column of dialog box)
Select Trust Center Setting (Middle Right of dialog box)
Select Macro Settings (Left column of dialog box)
Select Disable all macros with notification.
OK to close (twice I think) until closed.

Ensure that Developer ribbon is displayed. If not then:-
Click on Microsoft button (Large button top left of screen)
Select Excel Options (Towards bottom right of dialog box)
Select Popular (Left column). Should be default.
Under header €˜Top options for working with Excel
Check box for Show Developer tab in ribbon
OK to close.

Now you start:-

Open the workbook and select the worksheet that the macro is to process.

Only one button is required. The caption on the button will toggle between
Hide Rows and Unhide Rows.

Select Developer tab.

Select Insert (In the controls block)

Under ActiveX controls (Dont use forms controls), Click the Command Button.
(Hovering cursor over buttons will display their name.)

The cursor will turn to a plus sign. Move cursor onto your worksheet and
hold the left mouse button down while you drag the button out to the required
size. (Initially make it about 2 rows high by 2 columns wide. Can change
later if required.)

Right click the new command button you created and then select Properties.

Find TakeFocusOnClick in left column of dialog box (5 rows from bottom) and
click in the right column and then the drop down arrow and select False.
(This step not essential, I just like it better.)

Close the properties dialog box (X top right of dialog box)

Right click the your button again but this time select Format control.

Select Properties tab.

Select €˜Dont move or size with cells. (Essential when hiding and unhiding
rows)

Click OK to exit.

Right click the your button yet again but this time select View Code.

The VBA editor will open and the following 2 lines will be displayed in the
editor window:-


Private Sub CommandButton1_Click()

End Sub


Copy the code at the end of this guide and paste it in between the 2 lines.

If you already had any command buttons in the workbook then the Private Sub
name will show a CommandButton number greater than 1. If this is the case,
then edit the line of code following the green comment so that the
CommandButton number matches the number in the Private Sub name.

Close the VBA editor. (The cross in the red rectangle top right of screen)

On the Developer ribbon, Click on the Design button next to the Insert
button to close Design Mode.

Click your new button and it will unhide any rows (if hidden) and it will
initialize the caption on the button to Hide Rows. Click it again and it will
hide the rows and the caption will toggle to Unhide rows.

If you need to get back to the macro then Alt/F11 toggles between the
worksheet and the VBA editor. When the VBA editor opens, if the code is not
displayed then double click the sheet name where the button is located in the
Project Explorer in the left column.

If you want to alter any of the properties of the button like change the
font/color etc then on the Developer ribbon, Click the Design button and
right click your button and select properties. Dont forget to click Design
button again to turn off Design mode when finished. (If you get a plus sign
with arrows on it when you hover over the button, it is because Design Mode
is still turned on. Also if you click the button and you get the outline with
handles for changing the size then that also indicates it is still in Design
Mode).

The following is the code to copy. Ensure it goes between the existing lines
(Private Sub and End Sub) in the VBA editor:-


Dim objButton As Object
Dim rng As Range
Dim i As Long

'Edit the following CommandButton number
'if the Private Sub CommandButton number is not 1
Set objButton = ActiveSheet.CommandButton1

If objButton.Caption = "Hide Rows" Then
With ActiveSheet
Set rng = Range(.Cells(18, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

With rng
For i = 1 To .Rows.Count Step 4
If .Cells(i) = True Then
Range(.Cells(i), _
.Cells(i + 3)).EntireRow.Hidden = True
End If
Next i
End With

objButton.Caption = "Unhide Rows"

Else
ActiveSheet.Cells.EntireRow.Hidden = False
objButton.Caption = "Hide Rows"
End If

Application.Goto Range("A1"), Scroll:=True



Feel free to get back to me if you have any problems.

Regards,

OssieMac

Hi

This assumes your True false formula is in Col A and starts in A2
(adjust to suit). This should hide the entire column of all those
False entires.

Take care

Marcus


Sub Hide()
Dim Z As Long
Dim x As Range

Z = Range("A" & Rows.Count).End(xlUp).Row
Set x = Range("A2:A" & Z)

Application.ScreenUpdating = False
For Each cell In Range("A2:A" & Z)
cell.EntireRow.Hidden = cell.Value = False
Next cell

End Sub

Hello OssieMac,

I want to thank you very much for your detailed solution, it was very much
appreciated ... I implemented it over the weekend and it worked like a charm.

Well thought out, well written, well explained ... Kudoos to you. If ever
again I have a need for a solution I hope you are the one that provides it.

Thanks again,
Skymann.

"OssieMac" wrote:

Hi again Skymann,

I have had this reply ready for hours and was waiting on the info on what
cell to commence and I only just got the notification re your reply otherwise
you could have had it earlier. The reply by Marcus appears to only hide one
row where the True exists. I understood that you wanted to hide a group of 4
rows so that is what my macro does so Ill post it anyway.

You also indicated that you are a Newbie and didnt know where to start or
where to put the code which I interpreted as you requiring instructions so
the following guidelines for installing the macro might be overkill but I
think that is better than you having to wait on another answer if you are
unable to complete the task.

First thing that you need to do is ensure that you have macros enabled.
To do this in xl2007:-

Click on Microsoft button (Large button top left of screen)
Select Excel Options (Towards bottom right of dialog box)
Select Trust Centre (Left column of dialog box)
Select Trust Center Setting (Middle Right of dialog box)
Select Macro Settings (Left column of dialog box)
Select Disable all macros with notification.
OK to close (twice I think) until closed.

Ensure that Developer ribbon is displayed. If not then:-
Click on Microsoft button (Large button top left of screen)
Select Excel Options (Towards bottom right of dialog box)
Select Popular (Left column). Should be default.
Under header €˜Top options for working with Excel
Check box for Show Developer tab in ribbon
OK to close.

Now you start:-

Open the workbook and select the worksheet that the macro is to process.

Only one button is required. The caption on the button will toggle between
Hide Rows and Unhide Rows.

Select Developer tab.

Select Insert (In the controls block)

Under ActiveX controls (Dont use forms controls), Click the Command Button.
(Hovering cursor over buttons will display their name.)

The cursor will turn to a plus sign. Move cursor onto your worksheet and
hold the left mouse button down while you drag the button out to the required
size. (Initially make it about 2 rows high by 2 columns wide. Can change
later if required.)

Right click the new command button you created and then select Properties.

Find TakeFocusOnClick in left column of dialog box (5 rows from bottom) and
click in the right column and then the drop down arrow and select False.
(This step not essential, I just like it better.)

Close the properties dialog box (X top right of dialog box)

Right click the your button again but this time select Format control.

Select Properties tab.

Select €˜Dont move or size with cells. (Essential when hiding and unhiding
rows)

Click OK to exit.

Right click the your button yet again but this time select View Code.

The VBA editor will open and the following 2 lines will be displayed in the
editor window:-


Private Sub CommandButton1_Click()

End Sub


Copy the code at the end of this guide and paste it in between the 2 lines.

If you already had any command buttons in the workbook then the Private Sub
name will show a CommandButton number greater than 1. If this is the case,
then edit the line of code following the green comment so that the
CommandButton number matches the number in the Private Sub name.

Close the VBA editor. (The cross in the red rectangle top right of screen)

On the Developer ribbon, Click on the Design button next to the Insert
button to close Design Mode.

Click your new button and it will unhide any rows (if hidden) and it will
initialize the caption on the button to Hide Rows. Click it again and it will
hide the rows and the caption will toggle to Unhide rows.

If you need to get back to the macro then Alt/F11 toggles between the
worksheet and the VBA editor. When the VBA editor opens, if the code is not
displayed then double click the sheet name where the button is located in the
Project Explorer in the left column.

If you want to alter any of the properties of the button like change the
font/color etc then on the Developer ribbon, Click the Design button and
right click your button and select properties. Dont forget to click Design
button again to turn off Design mode when finished. (If you get a plus sign
with arrows on it when you hover over the button, it is because Design Mode
is still turned on. Also if you click the button and you get the outline with
handles for changing the size then that also indicates it is still in Design
Mode).

The following is the code to copy. Ensure it goes between the existing lines
(Private Sub and End Sub) in the VBA editor:-


Dim objButton As Object
Dim rng As Range
Dim i As Long

'Edit the following CommandButton number
'if the Private Sub CommandButton number is not 1
Set objButton = ActiveSheet.CommandButton1

If objButton.Caption = "Hide Rows" Then
With ActiveSheet
Set rng = Range(.Cells(18, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

With rng
For i = 1 To .Rows.Count Step 4
If .Cells(i) = True Then
Range(.Cells(i), _
.Cells(i + 3)).EntireRow.Hidden = True
End If
Next i
End With

objButton.Caption = "Unhide Rows"

Else
ActiveSheet.Cells.EntireRow.Hidden = False
objButton.Caption = "Hide Rows"
End If

Application.Goto Range("A1"), Scroll:=True



Feel free to get back to me if you have any problems.

Regards,

OssieMac