Excel VBA-hide row worksheet event
 

I am trying to use Visual Basic to automatically hide rows in Excel.
Basically, I have a sheet that has 50 rows (A12:A62)…each cell i
column A is linked to another sheet and comes back as a 0 or a
depending on pull-down options throughout the workbook. If the lin
comes back as a 1, I want the entire row to hide, otherwise, show th
row.

Also, I don’t want to have to run a macro. I want the rows t
hide/unhide whenever someone is looking at that sheet and changes som
criteria. I put the following code and it works if I manually chang
the value in A12, but when A12 is linked to another cell, the ro
doesn’t hide when the value changes.

Private Sub Worksheet_change(ByVal Target As Excel.Range)

If Target.Address = "$A$12" Then
If Target.Value = 1 Then
Range("a12").Select
Selection.EntireRow.Hidden = True
Else
Range("a12").Select
Selection.EntireRow.Hidden = False
End If
End If
End Sub

Does anybody know how to make this work when A12 is linked? Also
ultimately I will have to make this apply to 50 rows (A12:62). Ho
would I do this without repeating the above code for each row

--
Message posted from http://www.ExcelForum.com

Excel VBA-hide row worksheet event
 

How about whenever the sheet is activated?

Private Sub Worksheet_Activate()
Dim rng As Range

For Each rng In Range("A12:A62")
If rng.Value = 1 Then rng.EntireRow.Hidden = True
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"kabrahani " wrote in message
...
I am trying to use Visual Basic to automatically hide rows in Excel.
Basically, I have a sheet that has 50 rows (A12:A62)…each cell in
column A is linked to another sheet and comes back as a 0 or a 1
depending on pull-down options throughout the workbook. If the link
comes back as a 1, I want the entire row to hide, otherwise, show the
row.

Also, I don’t want to have to run a macro. I want the rows to
hide/unhide whenever someone is looking at that sheet and changes some
criteria. I put the following code and it works if I manually change
the value in A12, but when A12 is linked to another cell, the row
doesn’t hide when the value changes.

Private Sub Worksheet_change(ByVal Target As Excel.Range)

If Target.Address = "$A$12" Then
If Target.Value = 1 Then
Range("a12").Select
Selection.EntireRow.Hidden = True
Else
Range("a12").Select
Selection.EntireRow.Hidden = False
End If
End If
End Sub

Does anybody know how to make this work when A12 is linked? Also,
ultimately I will have to make this apply to 50 rows (A12:62). How
would I do this without repeating the above code for each row?


---
Message posted from http://www.ExcelForum.com/

Excel VBA-hide row worksheet event
 

Private Sub Worksheet_Calculate()

for each cell in me.Range("a12:A62")
if isnumeric(cell) then
if cell = 1 then
cell.entireRow.Hidden = True
Else
cell.EntireRow.Hidden = False
End if
End if
Next
End Sub

--
Regards,
Tom Ogilvy

"kabrahani " wrote in message
...
I am trying to use Visual Basic to automatically hide rows in Excel.
Basically, I have a sheet that has 50 rows (A12:A62).each cell in
column A is linked to another sheet and comes back as a 0 or a 1
depending on pull-down options throughout the workbook. If the link
comes back as a 1, I want the entire row to hide, otherwise, show the
row.

Also, I don't want to have to run a macro. I want the rows to
hide/unhide whenever someone is looking at that sheet and changes some
criteria. I put the following code and it works if I manually change
the value in A12, but when A12 is linked to another cell, the row
doesn't hide when the value changes.

Private Sub Worksheet_change(ByVal Target As Excel.Range)

If Target.Address = "$A$12" Then
If Target.Value = 1 Then
Range("a12").Select
Selection.EntireRow.Hidden = True
Else
Range("a12").Select
Selection.EntireRow.Hidden = False
End If
End If
End Sub

Does anybody know how to make this work when A12 is linked? Also,
ultimately I will have to make this apply to 50 rows (A12:62). How
would I do this without repeating the above code for each row?


---
Message posted from http://www.ExcelForum.com/