Help with macro to hide rows in excel on button push?

Question

I need a button activated macro to hide/show a set of rows. I am a beginner on VBA, and currently I have been working on the following, but it currently doesn't work:-

Sub Hide_initial()

Set hidden1923 = Range("B19:B23")
'rows I would like to hide

Range("B19:B19").Select
If cell.EntireRow.Hidden = true Then
For Each cell In hidden1923
EntireRow.Hidden = false
End If
Next

If cell.EntireRow.Hidden = False Then
For Each cell In hidden1923
EntireRow.Hidden = True
End If
Next


End Sub

Any help would be much appreciated!

Many thanks in advance

Cheers

Darren

Answer 1

You don't state whether you wish to use a 'Forms' command button or an ActiveX Control toolbox command button. For this example, we'll use the Forms toolbar.



Copy this macro to the clipboard:



Sub Toggle_Rows()

For Each cell In Range("B19:B23")

If cell.EntireRow.Hidden = False Then

cell.EntireRow.Hidden = True

Else

cell.EntireRow.Hidden = False

End If

Next

End Sub



Open the workbook and go to View > Toolbars and select the Forms toolbar.



When it appears, click the 'button' icon, then click in the worksheet and drag a button on to the worksheet.



The 'Assign Macro' window will open. Select this macro and click 'OK'.



You can position the button wherever you wish by dragging it, as well as size it by clicking and 'diagonally' dragging the corner. Right click it and 'Edit text' to change the default caption to what you wish to name the button.



Now, every time you click the button, the rows indicated will alternately 'hide/show'.

Answer 2

You're on the right track. You just have a few things out of place. Also, I would recommend changing the name of your sub. Some might mistake it for the Initialize event of a form since it is so close. Another thing you might want to do is to name the range B19:B23. If a user adds in new rows or deletes rows, those may not be the rows you want to reference anymore. By naming the range, the name will move to wherever the cells move to. To name the range, just select the cells then go to the Name Box above cell A1 and type in a name then press Enter.



The main reason your code isn't working is because you have your if statements starting before your For/Next statements. They should be more like this:







For Each cell In hidden1923



If cell.EntireRow.Hidden = true Then

EntireRow.Hidden = false

End If



Next





For Each cell In hidden1923



If cell.EntireRow.Hidden = False Then

EntireRow.Hidden = True

End If



Next



Another thing I want to mention is that you don't need the Select statement in your code. That doesn't do anything to help hide or unhid the cells.



Another big reason your code isn't working is because you have TWO IF statements and TWO FOR/NEXT statements. Basically when the 2nd For/Next statement runs, it would undo everything that your first For/Next statement did. So you need to combine that into ONE For/Next loop and to use and ElseIf statement for the ones that are not hidden to hide them. Oh yeah, and you need to declare a variable for hidden1923. You should declare it as a Range object. So basically here's how you'd want your code to look when it's all said and done:



Sub HideMyRows()



Dim hidden1923 As Range

Dim x As Range



'Name range B19:B23 as MyHiddenRows

Set hidden1923 = Range("MyHiddenRows")





For Each x In hidden1923



If x.EntireRow.Hidden = True Then



x.EntireRow.Hidden = False



ElseIf x.EntireRow.Hidden = False Then



x.EntireRow.Hidden = True



End If



Next x



End Sub





Also when you declare Next you have to put something after the Next. The way you had it written you would have needed to put "Next cell". I believe you also could have written your code like this and it would have worked:



Sub HideMyRows()



Dim hidden1923 As Range



'Name range B19:B23 as MyHiddenRows

Set hidden1923 = Range("MyHiddenRows")





For Each cell In hidden1923



If cell.EntireRow.Hidden = True Then



cell.EntireRow.Hidden = False



ElseIf cell.EntireRow.Hidden = False Then



cell.EntireRow.Hidden = True



End If



Next cell



End Sub



I'm not one to kind of just GIVE the person the answer. I'm more one where I like to teach them the right way to do it and WHY.

Answer 3

This is a very simple demo:



- Create a new Excel document (work book)

- Insert a button (Default Button1)

- Set the button click handler to



Sub Button1_Click ()

Dim range As range

Set range = Worksheets ("Sheet1").range ("B19:B19")



If range.Value = "Hello" Then

Worksheets ("Sheet1").Rows ("19").Hidden = True

range.Value = "Hidden"

ElseIf range.Value = "Hidden" Then

Worksheets ("Sheet1").Rows ("19").Hidden = False

range.Value = "Bye"

Else

Worksheets ("Sheet1").Rows ("19").Hidden = False

range.Value = "Hello"

End If



End Sub

Answer 4

Public Sub answer() 'do this Dim x As Integer For x = 0 To variety("D1"). _ CurrentRegion.Rows.count quantity - a million If variety("D1").Offset(x, 0). _ fee = 0 Then variety("A1:D1").Offset(x, 0).choose decision.EntireRow.Hidden = real end If next x end Sub