How would I combine button 1 and 2? Can I use a With?

[DDRA Steampunk]

I know I can combine more than one function on a button (as I have on each of these buttons), but I had no luck trying to combine the 2 buttons into 1. Seems like I should be able to do it with a "With" but I can't seem to make it work. I think it must have something to do with where I place the commands to hide and unhide the 2 sets of rows, but I just can't figure it out. I don't mind radical changes to how this button is coded or a completely different way of doing what I need to get done. I don't really understand how "calling" a macro or sub works so I haven't tried it. My VBA class textbook is like trying to read German when you've only learned French. I'm using Excel 2016. Suggestions appreciated

Public Sub ToggleButton1_Click()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
If ToggleButton1.Value = False Then
    With ActiveSheet
    BeginRow = 3
    EndRow = 102
    ChkCol = 18
    For RowCnt = BeginRow To EndRow
                Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    Next RowCnt
    End With
Else
        With ActiveSheet
    BeginRow = 3
    EndRow = 102
    ChkCol = 18

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 1 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
    End With
            With ActiveSheet
        Dim x As Integer
 
For x = 1 To ActiveSheet.UsedRange.Columns.Count
 
     Columns(x).EntireColumn.AutoFit
 
Next x
    End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Public Sub ToggleButton2_Click()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
If ToggleButton2.Value = False Then
    With ActiveSheet
    BeginRow = 124
    EndRow = 142
    ChkCol = 1
    For RowCnt = BeginRow To EndRow
                Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    Next RowCnt
    End With
Else
        With ActiveSheet
    BeginRow = 124
    EndRow = 142
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 1 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
    End With
            With ActiveSheet
        Dim x As Integer
 
For x = 1 To ActiveSheet.UsedRange.Columns.Count
 
     Columns(x).EntireColumn.AutoFit
 
Next x
    End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Marcelo Branco]

It seems the two codes are identical, therefore i would create a Sub - let's call it DoJob - to do what you need.
Each Button_Click code would just pass the parameters, rows, column and the button value (True or False) to sub DoJob.

Something like this (observe the . (dots) before .Cells, .Columns and .UsedRange)

Sub DoJob(BeginRow As Long, EndRow As Long, ChkCol As Long, bVal As Boolean)
    Dim RowCnt As Long, x As Long
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet
        If bVal = False Then
            For RowCnt = BeginRow To EndRow
                .Cells(RowCnt, ChkCol).EntireRow.Hidden = False
            Next RowCnt
        Else
            For RowCnt = BeginRow To EndRow
                If .Cells(RowCnt, ChkCol).Value < 1 Then
                    .Cells(RowCnt, ChkCol).EntireRow.Hidden = True
                Else
                    .Cells(RowCnt, ChkCol).EntireRow.Hidden = False
                End If
            Next RowCnt
        End If
        
        For x = 1 To .UsedRange.Columns.Count
            .Columns(x).EntireColumn.AutoFit
        Next x
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

And, for example, ToggleButton1_Click would be

Private Sub ToggleButton1_Click()
    DoJob 3, 102, 18, ToggleButton1.Value
End Sub

Hope this helps

M.

 

[DDRA Steampunk]

Private Sub ToggleButton1_Click()
DoJob 3, 102, 18, ToggleButton1.Value
End Sub
[/CODE]

Hope this helps

M.

Thanks You're correct in that both codes are exactly the same. I simply copied the code for button 1 to button 2 and updated the range. Your solution lets me copy code much easier from button to button (which is awesome, and I'll definitely use it!) but what I was looking for is how to make both target ranges execute on button #1 . Guess I wasn't clear.

 

[Fluff]

How about

Public Sub ToggleButton1_Click()
   Dim BeginRow As Variant
   Dim EndRow As Variant
   Dim ChkCol As Variant
   Dim RowCnt As Long
   Dim aryCnt As Long


   BeginRow = Array("3", "124")
   EndRow = Array("102", "142")
   ChkCol = Array("18", "1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
   If ToggleButton1.Value = False Then
      For aryCnt = 0 To 1
         ActiveSheet.Rows(BeginRow(aryCnt) & ":" & EndRow(aryCnt)).Hidden = False
      Next aryCnt
   Else
      With ActiveSheet
         For aryCnt = 0 To 1
            For RowCnt = BeginRow(aryCnt) To EndRow(aryCnt)
               If .Cells(RowCnt, CInt(ChkCol(aryCnt))).Value < 1 Then
                  .Cells(RowCnt, CInt(ChkCol(aryCnt))).EntireRow.Hidden = True
               Else
                  .Cells(RowCnt, CInt(ChkCol(aryCnt))).EntireRow.Hidden = False
               End If
            Next RowCnt
         Next aryCnt
         .UsedRange.EntireColumn.AutoFit
      End With
   End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[DDRA Steampunk]

Thanks, Fluff, it works perfectly I didn't know I could set up the multiple arrays like that, very clever!

 

[Fluff]

Glad to help & thanks for the feedback