PDA

View Full Version : Solved: I need help edit code hide rows in excel



etheer
05-17-2013, 10:26 AM
Hi
I have code hide rows in excel my problem in code i need close and open excel
for work code


Private Sub Worksheet_Calculate()
'For use with Commercial CF worksheet'
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Dim rCell As Range
For Each rCell In Range("B7:B450")
If rCell = "0" Then
rCell.EntireRow.Hidden = True
Else
rCell.EntireRow.Hidden = False
End If
Next rCell
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

:banghead::banghead::banghead:

patel
05-17-2013, 10:31 AM
what's your goal ?

etheer
05-17-2013, 11:53 AM
refresh sheet excel when change cells and hide the cells is no data

patel
05-17-2013, 11:10 PM
Private Sub Worksheet_Change(ByVal Target As Range)
For Each rCell In Range("B7:B450")
If rCell = "" Then
rCell.EntireRow.Hidden = True
Else
rCell.EntireRow.Hidden = False
End If
Next rCell
End Sub

HaHoBe
05-18-2013, 02:43 AM
Hi, etheer,

what is in the range you want to check: Formulas (as in your code) or values (like patels)? Worksheet_Change will only be triggered if you manually change values (any change in the workhseet would trigger that - not really what I would set that event for) as where Worksheet_Calculate could be refreshed by pressing F9 for Calculation.

Ciao,
Holger

etheer
05-18-2013, 06:08 PM
I need when change in cells in sheet1
Automatically sort and hide rows in sheet2

Teeroy
05-18-2013, 09:06 PM
Assuming you are talking about manual changes to Sheet1 (as opposed to changes made by VBA) I would run the code on the Worksheet_Activate event of Sheet2. e.g.

Private Sub Worksheet_Activate()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each rCell In Range("B7:B450")
If rCell = "" Or rCell = 0 Then
rCell.EntireRow.Hidden = True
Else
rCell.EntireRow.Hidden = False
End If
Next rCell
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

This will reduce processing overhead as you will only run it when you move between Sheets, make changes, and return.

etheer
05-18-2013, 11:44 PM
Assuming you are talking about manual changes to Sheet1 (as opposed to changes made by VBA) I would run the code on the Worksheet_Activate event of Sheet2. e.g.

Private Sub Worksheet_Activate()
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each rCell In Range("B7:B450")
If rCell = "" Or rCell = 0 Then
rCell.EntireRow.Hidden = True
Else
rCell.EntireRow.Hidden = False
End If
Next rCell
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
This will reduce processing overhead as you will only run it when you move between Sheets, make changes, and return.

Thank you so much code work 100%