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