Combine Macros to One Command Button

[shapeshiftingkiwi]

I'm trying to combine two macros to one command button but am getting Compile Error: Expected: Expression as an error. Here's what I'm trying to run...

Private Sub RunAllMacros()
CammandButton1_Click
HideRows_Based_On_Values
End Sub

Private Sub CommandButton1_Click()
Sheets("Form").Range("D7").Value = ""
Sheets("Form").Range("D9:D10").Value = ""
Sheets("Form").Range("F14:F29").Value = ""
Sheets("Form").Range("G14:G29").Value = ""
Sheets("Form").Range("C32:C39").Value = ""
Sheets("Form").Range("D32:D39").Value = ""
Sheets("Form").Range("F32:F39").Value = ""
Sheets("Form").Range("D43:D44").Value = ""
Sheets("Form").Range("H43:H44").Value = ""
Sheets("Form").Range("K43:K44").Value = ""
Sheets("Form").Range("D48").Value = ""
Sheets("Form").Range("I48").Value = ""
Sheets("Form").Range("D5").Value = ""
Sheets("Form").Range("D56").Value = ""
Sheets("Form").Range("I56").Value = ""
Sheets("Form").Range("D51:D52").Value = ""
Sheets("Form").Range("H51:H52").Value = ""
Sheets("Form").Range("K51:K52").Value = ""
Sheets("Form").Range("H5").Value = ""
Sheets("Form").Range("J5").Value = ""

MsgBox "Form has been reset"

End Sub

Private Sub HideRows_Based_On_Values()

For Each cell In Range("D14:D33")
If cell.Value = "-" Then cell.EntireRow.Hidden = True
If cell.Value <> "-" Then cell.EntireRow.Hidden = False
End Sub

End Sub


If this code looks like absolute trash, please forgive me, this is all very new to me!

Thanks for any help!

 

[tj4242]

I noticed 3 things right away.

1. CammandButton1_Click is spelled wrong. The second letter is "a" when it should be "o"
2. There is an extra "End Sub" at the end
3. There is no "Next" to match the For

 

[shapeshiftingkiwi]

I noticed 3 things right away.

1. CammandButton1_Click is spelled wrong. The second letter is "a" when it should be "o"
2. There is an extra "End Sub" at the end
3. There is no "Next" to match the For

I had spotted the "cammand" typo, I've removed the extra end sub. What is point 3 referring to on the Next? I'm following this guide for hiding the rows and the macro works on it's own.

 

[tj4242]

A For statement requires a Next for the loop.

For Each cell In Range("D14:D33")
   If cell.Value = "-" Then cell.EntireRow.Hidden = True
   If cell.Value <> "-" Then cell.EntireRow.Hidden = False
Next

 

[tj4242]

I missed that each IF also needs an End IF

For Each cell In Range("D14:D33")
   If cell.Value = "-" Then cell.EntireRow.Hidden = True End If
   If cell.Value <> "-" Then cell.EntireRow.Hidden = False End If
Next

 

[shapeshiftingkiwi]

Ohh jeez somehow I copied the wrong code. I had that in there as well. So just the duplicate end sub which I changed and still no luck...

Private Sub RunAllMacros()
CommandButton1_Click
HideRows_Based_On_Values
End Sub

Private Sub CommandButton1_Click()
Sheets("Form").Range("D7").Value = ""
Sheets("Form").Range("D9:D10").Value = ""
Sheets("Form").Range("F14:F29").Value = ""
Sheets("Form").Range("G14:G29").Value = ""
Sheets("Form").Range("C36:C51").Value = ""
Sheets("Form").Range("D36:D51").Value = ""
Sheets("Form").Range("F36:F51").Value = ""
Sheets("Form").Range("D43:D44").Value = ""
Sheets("Form").Range("H43:H44").Value = ""
Sheets("Form").Range("K43:K44").Value = ""
Sheets("Form").Range("D48").Value = ""
Sheets("Form").Range("I48").Value = ""
Sheets("Form").Range("D5").Value = ""
Sheets("Form").Range("D56").Value = ""
Sheets("Form").Range("I56").Value = ""
Sheets("Form").Range("D51:D52").Value = ""
Sheets("Form").Range("H51:H52").Value = ""
Sheets("Form").Range("K51:K52").Value = ""
Sheets("Form").Range("H5").Value = ""
Sheets("Form").Range("J5").Value = ""

MsgBox "Form has been reset"

End Sub

Private Sub HideRows_Based_On_Values()

For Each cell In Sheets("Form").Range("D14:D33")
If cell.Value = "-" Then cell.EntireRow.Hidden = True
Next cell
End Sub

 

[tj4242]

It worked perfectly for me. The only change I made was from Private Sub RunAllMacros() to Public Sub RunAllMacros() so that I could run it from the Macros button.

Before

Book3
	A	B	C	D	E
20					20
21					21
22					22
23				-	23
24				-	24
25					25
26					26
27					27
28					28
29					29
30					30
31					31
Form

You can see lines 23 and 24 are visible. Run the macro and they are gone

Book3
	A	B	C	D	E
20					20
21					21
22					22
25					25
26					26
27					27
28					28
29					29
30					30
31					31
Form

 

[shapeshiftingkiwi]

Got it! Thank you!