VBA Autohide Help

R

r2badd

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
 
J

Jim Cone

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" <[email protected]>
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
 
J

Jim Cone

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



"r2badd" <[email protected]>
wrote in message
I changed the code to "+ 9" and it worked. Thank you Jim!
 
R

r2badd

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

"r2badd" <[email protected]>
wrote in message
I changed the code to  "+ 9" and it worked. Thank you Jim!
Click to expand...

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
 
J

Jim Cone

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" <[email protected]>
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
 
R

r2badd

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
insert rows--->protected sheet 1
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Dependent drop down dynamic range formula, off sheet. 7
Macro - Next blank row 3
This Macro halts any ideas what could be wrong 4
Alternate Row Select in Sheet 1 then Paste to Sheet 2 4
VBA group the number in the Column and Copy to the new worksheet. 0

Top