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 I’ll post it anyway.
You also indicated that you are a Newbie and didn’t 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 (Don’t 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 ‘Don’t 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. Don’t 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