How to combine 4 private sub in one sheet?

[Jamerson]

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

 

[bertie]

I am heading out shortly so this will be my last contribution until about after 6pm.

If you decide to use the change event from the last post you will need a procedure to unhide rows for future processing

[color=darkblue]Public[/color] [color=darkblue]Sub[/color] UnHideRows()
  Rows("5:11").EntireRow.Hidden = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

This Public procedure can be called from Excel via the Tools => Macro menu.

 

[Jamerson]

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.

[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]

sorry, I still don't get it.

I have two sheets. "sheet1" has the VBA code. "sheet2" is for data entry.

If I had only this quote in "sheet1":

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

after I enter data in "sheet2", all the zero will be hiden.

This was what I had been doing. However, after I tried to add 4 private sub, it stop working.

And after I combine the 4 private sub, it still unable to automatically hide/show.

 

[bertie]

Sorry, I still don't get it.

In all honesty, I am struggling with what you are trying to accomplish here.

So let's take a deep breath, and we will get there.

Walk me through how you process this data.

You do this on sheet ?, this happens on sheet ??
You do this on sheet ?, this happens on sheet ??

Name the sheets we are working with an shame the devil.

 

[Jamerson]

Thanks for the help.

I've got the solution for it.

insteat of

Private Sub worksheet_1()
...
End Sub

I changed it to

Private Sub Worksheet_Activate()
...
End Sub

 

[Jamerson]

To be honest here, I'm new to VBA. I am just copying from the codes given.

May I know how to combine these in RED?

Private Sub worksheet_activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
Dim aFirstRow() As Variant
Dim aLastRow() As Variant
Dim i As Integer 'loop variable

'arrays must have same dimensions
aFirstRow = Array(56, 72, 92, 113)
aLastRow = Array(68, 87, 106, 134)


'*****************************
'< Set your columns that contain data >
Const FirstCol As String = "B"
Const LastCol As String = "B"
'*****************************
'ActiveWindow.DisplayZeros = True
'Application.ScreenUpdating = False
'loop through the control array
For i = LBound(aFirstRow) To UBound(aFirstRow)

For HiddenRow = aFirstRow(i) To aLastRow(i)
'(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)

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
Next i

'Application.ScreenUpdating = True
End Sub

Private Sub worksheet_activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
Dim aFirstRow() As Variant
Dim aLastRow() As Variant
Dim i As Integer 'loop variable

'arrays must have same dimensions
aFirstRow = Array(10, 30)
aLastRow = Array(20, 50)


'*****************************
'< Set your columns that contain data >
Const FirstCol As String = "H"
Const LastCol As String = "H"
'*****************************
'ActiveWindow.DisplayZeros = True
'Application.ScreenUpdating = False
'loop through the control array
For i = LBound(aFirstRow) To UBound(aFirstRow)

For HiddenRow = aFirstRow(i) To aLastRow(i)
'(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)

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
Next i

'Application.ScreenUpdating = True
End Sub

 

[Jamerson]

Solved!

I added the folowing code to the top:

Private Sub worksheet_activate()

Call Worksheet_Activate_Part1
Call Worksheet_Activate_Part2

End Sub