How to combine 4 private sub in one sheet?

Jamerson · Jun 29, 2011

I am running on 2007, how can I combine 4 private sub in one sheet so that it will auto run?

Private Sub worksheet_1()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const firstrow As Long = 56
Const LastRow As Long = 68

'< Set your columns that contain data >
Const FirstCol As String = "B"
Const LastCol As String = "B"
'*****************************

ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = False

For HiddenRow = firstrow To LastRow

'(we're using columns B here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True

End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

Private Sub worksheet_1()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const firstrow As Long = 72
Const LastRow As Long = 87

'< Set your columns that contain data >
Const FirstCol As String = "B"
Const LastCol As String = "B"
'*****************************

ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = False

For HiddenRow = firstrow To LastRow

'(we're using columns B here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True

End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

Private Sub worksheet_1()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const firstrow As Long = 92
Const LastRow As Long = 106

'< Set your columns that contain data >
Const FirstCol As String = "B"
Const LastCol As String = "B"
'*****************************

ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = False

For HiddenRow = firstrow To LastRow

'(we're using columns B here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True

End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

Private Sub worksheet_1()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const firstrow As Long = 113
Const LastRow As Long = 134

'< Set your columns that contain data >
Const FirstCol As String = "B"
Const LastCol As String = "B"
'*****************************

ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = False

For HiddenRow = firstrow To LastRow

'(we're using columns B here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True

End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

taigovinda · Jun 30, 2011

It looks like this is the only part of your code that changes each time (I didn't bother to look too closely at what the rest is doing...):

Rich (BB code):

Private Sub worksheet_1()
 
Dim HiddenRow&, RowRange As Range, RowRangeValue&
 
'*****************************
'< Set the 1st & last rows to be hidden >
Const firstrow As Long = 56
Const LastRow As Long = 68
 
 
'< Set your columns that contain data >

If that is in fact the only part of the code that changes, try altering the top part of your sub to look like this so that it will accept two arguments:

Rich (BB code):

Private Sub worksheet_1(firstrow as Long, LastRow as Long)
 
Dim HiddenRow&, RowRange As Range, RowRangeValue&
 
'*****************************
'< Set the 1st & last rows to be hidden >
'''Const firstrow As Long = 56 '''commented out this row
'''Const LastRow As Long = 68 '''commented out this row
 
 
'< Set your columns that contain data >

...and then call your sub repeatedly like this:

Rich (BB code):

sub CallMySub
'run the sub four times, passing in the different values
worksheet_1 56, 68
worksheet_1 72, 87
worksheet_1 92, 106
worksheet_1 113, 134
 
end sub

So now, running CallMySub will do all four pieces... Note, if you type in the CallMySub code (instead of pasting it in) you'll see that the Visual Basic Editor's Intellisense will automatically let you know what arguments it needs. That is, as you type, it will pop up a little ghost text saying that it is expecting 'firstrow as Long' and then 'LastRow as Long'

Hope that helps.

Tai

bertie · Jun 30, 2011

I have written it so I will post it.

The way I would approach your problem would to use arrays to store your start and stop values and use these as a control for you code. See the highlighted code below.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] worksheet_1()
  [COLOR=darkblue]Dim[/COLOR] HiddenRow&, RowRange [COLOR=darkblue]As[/COLOR] Range, RowRangeValue&
[COLOR=red]Dim aFirstRow() As Variant[/COLOR]
[COLOR=red]Dim aLastRow() As Variant[/COLOR]
[COLOR=red]Dim i As Integer    'loop variable[/COLOR]
 
[COLOR=red]'arrays must have same dimensions[/COLOR]
[COLOR=red]aFirstRow = Array(56, 72, 92, 113)[/COLOR]
[COLOR=red]aLastRow = Array(68, 87, 106, 134)[/COLOR]
 
 
  [COLOR=green]'*****************************[/COLOR]
  [COLOR=green]'< Set your columns that contain data >[/COLOR]
  [COLOR=darkblue]Const[/COLOR] FirstCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "B"
  [COLOR=darkblue]Const[/COLOR] LastCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "B"
  [COLOR=green]'*****************************[/COLOR]
[COLOR=green]'ActiveWindow.DisplayZeros = True[/COLOR]
[COLOR=green]'Application.ScreenUpdating = False[/COLOR]
  [COLOR=green]'loop through the control array[/COLOR]
  [COLOR=red]For i = LBound(aFirstRow) To UBound(aFirstRow)[/COLOR]
 
      [COLOR=darkblue]For[/COLOR] HiddenRow = [COLOR=red]aFirstRow(i) To aLastRow(i)[/COLOR]
        [COLOR=green]'(we're using columns B here)[/COLOR]
        [COLOR=darkblue]Set[/COLOR] RowRange = Range(FirstCol & HiddenRow & _
        ":" & LastCol & HiddenRow)
 
        [COLOR=green]'sums the entries in cells in the RowRange[/COLOR]
        RowRangeValue = Application.Sum(RowRange)
 
        [COLOR=darkblue]If[/COLOR] RowRangeValue <> 0 [COLOR=darkblue]Then[/COLOR]
          [COLOR=green]'there's something in this row - don't hide[/COLOR]
          Rows(HiddenRow).EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
          [COLOR=green]'there's nothing in this row yet - hide it[/COLOR]
          Rows(HiddenRow).EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Next[/COLOR] HiddenRow
  [COLOR=red]Next i[/COLOR]
 
[COLOR=green]'Application.ScreenUpdating = True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

NB I have commented out screen updating for testing.

Jamerson · Jun 30, 2011

taigovinda said:
It looks like this is the only part of your code that changes each time (I didn't bother to look too closely at what the rest is doing...):

Rich (BB code):

Private Sub worksheet_1() Dim HiddenRow&, RowRange As Range, RowRangeValue& '***************************** '< Set the 1st & last rows to be hidden > Const firstrow As Long = 56 Const LastRow As Long = 68 '< Set your columns that contain data >

If that is in fact the only part of the code that changes, try altering the top part of your sub to look like this so that it will accept two arguments:

Rich (BB code):

Private Sub worksheet_1(firstrow as Long, LastRow as Long) Dim HiddenRow&, RowRange As Range, RowRangeValue& '***************************** '< Set the 1st & last rows to be hidden > '''Const firstrow As Long = 56 '''commented out this row '''Const LastRow As Long = 68 '''commented out this row '< Set your columns that contain data >

...and then call your sub repeatedly like this:

Rich (BB code):

sub CallMySub 'run the sub four times, passing in the different values worksheet_1 56, 68 worksheet_1 72, 87 worksheet_1 92, 106 worksheet_1 113, 134 end sub

So now, running CallMySub will do all four pieces... Note, if you type in the CallMySub code (instead of pasting it in) you'll see that the Visual Basic Editor's Intellisense will automatically let you know what arguments it needs. That is, as you type, it will pop up a little ghost text saying that it is expecting 'firstrow as Long' and then 'LastRow as Long'

Hope that helps.

Tai

Yes, you are correct. Only the following change:

Const firstrow As Long =
Const LastRow As Long =

Jamerson · Jun 30, 2011

bertie said:

I have written it so I will post it.

The way I would approach your problem would to use arrays to store your start and stop values and use these as a control for you code. See the highlighted code below.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] worksheet_1()
  [COLOR=darkblue]Dim[/COLOR] HiddenRow&, RowRange [COLOR=darkblue]As[/COLOR] Range, RowRangeValue&
[COLOR=red]Dim aFirstRow() As Variant[/COLOR]
[COLOR=red]Dim aLastRow() As Variant[/COLOR]
[COLOR=red]Dim i As Integer    'loop variable[/COLOR]
 
[COLOR=red]'arrays must have same dimensions[/COLOR]
[COLOR=red]aFirstRow = Array(56, 72, 92, 113)[/COLOR]
[COLOR=red]aLastRow = Array(68, 87, 106, 134)[/COLOR]
 
 
  [COLOR=green]'*****************************[/COLOR]
  [COLOR=green]'< Set your columns that contain data >[/COLOR]
  [COLOR=darkblue]Const[/COLOR] FirstCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "B"
  [COLOR=darkblue]Const[/COLOR] LastCol [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR] = "B"
  [COLOR=green]'*****************************[/COLOR]
[COLOR=green]'ActiveWindow.DisplayZeros = True[/COLOR]
[COLOR=green]'Application.ScreenUpdating = False[/COLOR]
  [COLOR=green]'loop through the control array[/COLOR]
  [COLOR=red]For i = LBound(aFirstRow) To UBound(aFirstRow)[/COLOR]
 
      [COLOR=darkblue]For[/COLOR] HiddenRow = [COLOR=red]aFirstRow(i) To aLastRow(i)[/COLOR]
        [COLOR=green]'(we're using columns B here)[/COLOR]
        [COLOR=darkblue]Set[/COLOR] RowRange = Range(FirstCol & HiddenRow & _
        ":" & LastCol & HiddenRow)
 
        [COLOR=green]'sums the entries in cells in the RowRange[/COLOR]
        RowRangeValue = Application.Sum(RowRange)
 
        [COLOR=darkblue]If[/COLOR] RowRangeValue <> 0 [COLOR=darkblue]Then[/COLOR]
          [COLOR=green]'there's something in this row - don't hide[/COLOR]
          Rows(HiddenRow).EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
          [COLOR=green]'there's nothing in this row yet - hide it[/COLOR]
          Rows(HiddenRow).EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Next[/COLOR] HiddenRow
  [COLOR=red]Next i[/COLOR]
 
[COLOR=green]'Application.ScreenUpdating = True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

NB I have commented out screen updating for testing.

Thanks for the help. This code works!

However, now I have another situation, I need to click the “run sub/userform” button in the Visual Basic to run this code. Is there a way it will automatically run by itself?

bertie · Jul 1, 2011

If you mean to run when the workbook is open then you place a call to the procedure in the ThisWorkbook module.

Code:

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
  worksheet_1
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

But,
"worksheet_1" is a Private procedure. For the above code to work, "worksheet_1" would also need to be in the ThisWorkbook module.

If "worksheet_1" is stored in a module other than ThisWorkbook the Private statement will hide it as it is supposed to do, so change this to Public.

Edit: Changing "worksheet_1" to a Public procedure will also make it visible from the Tools => Macro menu in Excel.

Jamerson · Jul 1, 2011

bertie said:
If you mean to run when the workbook is open then you place a call to the procedure in the ThisWorkbook module.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open() worksheet_1 [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

But,
"worksheet_1" is a Private procedure. For the above code to work, "worksheet_1" would also need to be in the ThisWorkbook module.

If "worksheet_1" is stored in a module other than ThisWorkbook the Private statement will hide it as it is supposed to do, so change this to Public.

Edit: Changing "worksheet_1" to a Public procedure will also make it visible from the Tools => Macro menu in Excel.

Thank you for your prompt reply.

If I only have one code here

Private Sub worksheet_1()

Dim HiddenRow&, RowRange As Range, RowRangeValue&

'*****************************
'< Set the 1st & last rows to be hidden >
Const firstrow As Long = 56
Const LastRow As Long = 68

'< Set your columns that contain data >
Const FirstCol As String = "B"
Const LastCol As String = "B"
'*****************************

ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = False

For HiddenRow = firstrow To LastRow

'(we're using columns B here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)

'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)

If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True

End If

Next HiddenRow

Application.ScreenUpdating = True

End Sub

When I key data in worksheet_2, worksheet_1 will auto show/hide.

bertie · Jul 1, 2011

When I key data in worksheet_2, worksheet_1 will auto show/hide.

I think you may be looking for a Worksheet_Change event.

For example, if you wanted the "worksheet1" procedure to run when you change the value in cell "A1" then:

Code:

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
  
  [color=darkblue]If[/color] Target.Address = "$A$1" [color=darkblue]Then[/color]
    worksheet_1
  [color=darkblue]End[/color] [color=darkblue]If[/color]
  
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Both the Worksheet_Change code and the code for "worksheet_1" procedure would be in the same sheet module.

If I have picked you up wrong, walk me through what you do with this file each time.

Bertie

Jamerson · Jul 1, 2011

bertie said:
I think you may be looking for a Worksheet_Change event.

For example, if you wanted the "worksheet1" procedure to run when you change the value in cell "A1" then:

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range) [COLOR=darkblue]If[/COLOR] Target.Address = "$A$1" [COLOR=darkblue]Then[/COLOR] worksheet_1 [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Both the Worksheet_Change code and the code for "worksheet_1" procedure would be in the same sheet module.

If I have picked you up wrong, walk me through what you do with this file each time.

Bertie

If I had data in worksheet2 as follow:

<table border="0" cellpadding="0" cellspacing="0" width="83"><colgroup><col style="mso-width-source:userset;mso-width-alt:1080;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:512;width:14pt" width="18"> <col style="mso-width-source:userset;mso-width-alt:768;width:20pt" width="27"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:29pt" height="20" width="38">A1</td> <td style="width:14pt" width="18">=</td> <td style="width:20pt" align="right" width="27">5</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A2</td> <td>=</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A3</td> <td>=</td> <td align="right">10</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A4</td> <td>=</td> <td align="right">6</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A5</td> <td>=</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A6</td> <td>=</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A7</td> <td>=</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A8</td> <td>=</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A9</td> <td>=</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">A10</td> <td>=</td> <td align="right">5</td> </tr> </tbody></table>

In worksheet1, it will automatically hide all the zero without me doing anything.

bertie · Jul 1, 2011

So using this scenario. You type in the numbers. And when you enter a zero you want the row to be hidden.

Place this code in the module for the sheet.

Code:

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
  [color=darkblue]If[/color][COLOR="Red"] Target.Column <> 1[/COLOR] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
  
  [color=darkblue]If[/color] Target.Value = 0 [color=darkblue]Then[/color]
    Rows(Target.Row).EntireRow.Hidden = [color=darkblue]True[/color]
  [color=darkblue]End[/color] [color=darkblue]If[/color]
  
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

How to combine 4 private sub in one sheet?

Jamerson

New Member

Excel Facts

taigovinda

Well-known Member

bertie

Well-known Member

Jamerson

New Member

Jamerson

New Member

bertie

Well-known Member

Jamerson

New Member

bertie

Well-known Member

Jamerson

New Member

bertie

Well-known Member

Similar threads

Forum statistics

Share this page

How to combine 4 private sub in one sheet?

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock