# Combine Macros to One Command Button



## shapeshiftingkiwi (Dec 20, 2022)

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 (Dec 20, 2022)

I noticed 3 things right away.

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


----------



## shapeshiftingkiwi (Dec 20, 2022)

tj4242 said:


> I noticed 3 things right away.
> 
> CammandButton1_Click is spelled wrong.  The second letter is "a" when it should be "o"
> There is an extra "End Sub" at the end
> 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 (Dec 20, 2022)

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 (Dec 20, 2022)

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 (Dec 20, 2022)

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 (Dec 20, 2022)

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
Book3ABCDE20202121222223-2324-242525262627272828292930303131Form

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


----------



## shapeshiftingkiwi (Dec 20, 2022)

Got it! Thank you!


----------

