Ok, first post here so take it easy on me. I am trying to auto hide
rows on a worksheet, unless there are values in rows on another
worksheet in the same excel document. For example, if Sheet 2, row 10
has a value in column A, B or C, then I want Sheet 1 to show those
values in row 20, column A, B & C. (This part of my forumula works) If
Sheet 2, row 11, has no value in column A, B or C, then I want to hide
row 21 on Sheet 1. (This part is not working).

I found the below formula online and can modify it to any range of
cells I want and the data will copy over from Sheet 2 to Sheet 1 into
the correct cell. However, the autohide starts at Sheet 1, row 1 every
time, and I cannot figure out how to make the autohide start at row
20.

Thank you!

Private Sub Worksheet_Activate()
Dim Rng As Range
Dim i As Long
Set Rng = Sheets("Sheet2").Range("A20:C25")
Application.ScreenUpdating = False
Sheets("Sheet1").Range("A10:C15").Value = Rng.Value
For i = 1 To Rng.Rows.Count
If WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
Sheets("Sheet1").Rows(i).EntireRow.Hidden = True
Else
Sheets("Sheet1").Rows(i).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True
End Sub

What you have is code - not formula.
Try changing these two lines...

Sheets("Sheet1").Rows(i).EntireRow.Hidden = True
Sheets("Sheet1").Rows(i).EntireRow.Hidden = False
-to-
Sheets("Sheet1").Rows(i + 10).EntireRow.Hidden = True
Sheets("Sheet1").Rows(i + 10).EntireRow.Hidden = False
--
Jim Cone
Portland, Oregon USA


"r2badd"
wrote in message
Ok, first post here so take it easy on me. I am trying to auto hide
rows on a worksheet, unless there are values in rows on another
worksheet in the same excel document. For example, if Sheet 2, row 10
has a value in column A, B or C, then I want Sheet 1 to show those
values in row 20, column A, B & C. (This part of my forumula works) If
Sheet 2, row 11, has no value in column A, B or C, then I want to hide
row 21 on Sheet 1. (This part is not working).

I found the below formula online and can modify it to any range of
cells I want and the data will copy over from Sheet 2 to Sheet 1 into
the correct cell. However, the autohide starts at Sheet 1, row 1 every
time, and I cannot figure out how to make the autohide start at row
20.

Thank you!

Private Sub Worksheet_Activate()
Dim Rng As Range
Dim i As Long
Set Rng = Sheets("Sheet2").Range("A20:C25")
Application.ScreenUpdating = False
Sheets("Sheet1").Range("A10:C15").Value = Rng.Value
For i = 1 To Rng.Rows.Count
If WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
Sheets("Sheet1").Rows(i).EntireRow.Hidden = True
Else
Sheets("Sheet1").Rows(i).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True
End Sub

I changed the code to "+ 9" and it worked. Thank you Jim!

You are welcome. Feedback is appreciated.
--
Jim Cone
Portland, Oregon USA



"r2badd"
wrote in message
I changed the code to "+ 9" and it worked. Thank you Jim!

On Jul 31, 9:48*am, "Jim Cone" wrote:
You are welcome. *Feedback is appreciated.
--
Jim Cone
Portland, Oregon *USA

"r2badd"
wrote in message
I changed the code to *"+ 9" and it worked. Thank you Jim!

One more question on this... I am trying to do this code for multiple
worksheets going into the same first worksheet. I am not sure how to
get the second half of code working. Below is the code I created, the
first part works perfectly, but the second half doesn't work at all.


Sub Worksheet_Activate()
'
'AutoFill&Hide for Incidents
'
Dim Rng As Range
Dim i As Long
Set Rng = Sheets("ALLEN").Range("B20:E26")
Application.ScreenUpdating = False
Sheets("Report").Range("B18:E24").Value = Rng.Value
For i = 1 To Rng.Rows.Count
If WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
Sheets("Report").Rows(i + 17).EntireRow.Hidden = True
Else
Sheets("Report").Rows(i + 17).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True
End Sub
__________________________________________________ __________________
Sub Worksheet_Activate_Incident_Alyce()
'
'AutoFill&Hide for Incidents
'
Dim Rng2 As Range2
Dim i As Long
Set Rng2 = Sheets("ALYCE").Range2("B20:E26")
Application.ScreenUpdating = False
Sheets("Report").Range("B25:E31").Value = Rng2.Value
For i = 1 To Rng2.Rows.Count
If WorksheetFunction.CountA(Rng2.Rows(i)) = 0 Then
Sheets("Report").Rows(i + 24).EntireRow.Hidden = True
Else
Sheets("Report").Rows(i + 24).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True
End Sub

Your original code is "event" code. When the sheet is selected the code runs.
You can't have multiple sets of identical event code operating in a single sheet.
But you can within that single event sub call other code sets (subs or functions).
So rename the second sub... maybe. "DoTheOtherSheets" and place it
in a standard/regular module (not a module behind a sheet). Then add the call line
near the bottom of the Worksheet_Activate sub...

' your other code
Call DoTheOtherSheets
Application.ScreenUpdating = True
End Sub
'--
Also change...
Dim Rng2 As Range2
-to-
Dim Rng2 As Range
--
Jim Cone
Portland, Oregon USA





"r2badd"
wrote in message
One more question on this... I am trying to do this code for multiple
worksheets going into the same first worksheet. I am not sure how to
get the second half of code working. Below is the code I created, the
first part works perfectly, but the second half doesn't work at all.


Sub Worksheet_Activate()
'
'AutoFill&Hide for Incidents
'
Dim Rng As Range
Dim i As Long
Set Rng = Sheets("ALLEN").Range("B20:E26")
Application.ScreenUpdating = False
Sheets("Report").Range("B18:E24").Value = Rng.Value
For i = 1 To Rng.Rows.Count
If WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
Sheets("Report").Rows(i + 17).EntireRow.Hidden = True
Else
Sheets("Report").Rows(i + 17).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True
End Sub
__________________________________________________ __________________
Sub Worksheet_Activate_Incident_Alyce()
'
'AutoFill&Hide for Incidents
'
Dim Rng2 As Range2
Dim i As Long
Set Rng2 = Sheets("ALYCE").Range2("B20:E26")
Application.ScreenUpdating = False
Sheets("Report").Range("B25:E31").Value = Rng2.Value
For i = 1 To Rng2.Rows.Count
If WorksheetFunction.CountA(Rng2.Rows(i)) = 0 Then
Sheets("Report").Rows(i + 24).EntireRow.Hidden = True
Else
Sheets("Report").Rows(i + 24).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True
End Sub

Well it took me about 20 minutes to realize I needed to call the sub
name and not the module name, but it is working now. Thank you so much
Jim!

On Jul 31, 12:35*pm, "Jim Cone" wrote:
Your original code is "event" code. *When the sheet is selected the code runs.
You can't have multiple sets of identical event code operating in a single sheet.
But you can within that single event sub call other code sets (subs or functions).
So rename the second sub... maybe. "DoTheOtherSheets" and place it
in a standard/regular module (not a module behind a sheet). *Then add the call line
near the bottom of the Worksheet_Activate sub...

' your other code
Call DoTheOtherSheets
Application.ScreenUpdating = True
End Sub
'--
Also change...
Dim Rng2 As Range2
-to-
Dim Rng2 As Range
--
Jim Cone
Portland, Oregon *USA

"r2badd"
wrote in message
One more question on this... I am trying to do this code for multiple
worksheets going into the same first worksheet. I am not sure how to
get the second half of code working. Below is the code I created, the
first part works perfectly, but the second half doesn't work at all.

Sub Worksheet_Activate()
'
'AutoFill&Hide for Incidents
'
Dim Rng As Range
Dim i As Long
Set Rng = Sheets("ALLEN").Range("B20:E26")
Application.ScreenUpdating = False
Sheets("Report").Range("B18:E24").Value = Rng.Value
For i = 1 To Rng.Rows.Count
* * If WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
* * * * Sheets("Report").Rows(i + 17).EntireRow.Hidden = True
* * Else
* * * * Sheets("Report").Rows(i + 17).EntireRow.Hidden = False
* * End If
Next i

Application.ScreenUpdating = True
End Sub
__________________________________________________ __________________
Sub Worksheet_Activate_Incident_Alyce()
'
'AutoFill&Hide for Incidents
'
Dim Rng2 As Range2
Dim i As Long
Set Rng2 = Sheets("ALYCE").Range2("B20:E26")
Application.ScreenUpdating = False
Sheets("Report").Range("B25:E31").Value = Rng2.Value
For i = 1 To Rng2.Rows.Count
* * If WorksheetFunction.CountA(Rng2.Rows(i)) = 0 Then
* * * * Sheets("Report").Rows(i + 24).EntireRow.Hidden = True
* * Else
* * * * Sheets("Report").Rows(i + 24).EntireRow.Hidden = False
* * End If
Next i

Application.ScreenUpdating = True
End Sub