MattHammer
New Member
- Joined
- Apr 21, 2010
- Messages
- 4
Hi all,
I am trying to hide rows in multiple worksheets with multiple criteria. I have a code that works it just takes a few hours on an idle machine to run. Any suggestions to make the following code run faster?
Sub Table7RowHide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim c As Object
Dim range1 As range
Dim range2 As range
Dim range3 As range
Dim range4 As range
Dim range5 As range
Dim range6 As range
Dim range7 As range
Dim range8 As range
Dim range9 As range
Dim range10 As range
Dim range11 As range
'If sheet names are different/excluded modify/delete set
'command below and hide command
Set range1 = Sheets("xxx").range("f10:f2000")
Set range2 = Sheets("xxx").range("f10:f2000")
Set range3 = Sheets("xxx").range("f10:f2000")
Set range4 = Sheets("xxx").range("f10:f2000")
Set range5 = Sheets("xxx").range("f10:f2000")
Set range6 = Sheets("xxx").range("f10:f2000")
Set range7 = Sheets("xxx").range("f10:f2000")
Set range8 = Sheets("xxx").range("f10:f2000")
Set range9 = Sheets("xxx").range("f10:f2000")
Set range10 = Sheets("xxx").range("f10:f2000")
Set range11 = Sheets("xxx").range("e26:e160")
For Each c In range1
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range2
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range3
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range4
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range5
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range6
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range7
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range8
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range9
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range10
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range11
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub
I am trying to hide rows in multiple worksheets with multiple criteria. I have a code that works it just takes a few hours on an idle machine to run. Any suggestions to make the following code run faster?
Sub Table7RowHide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim c As Object
Dim range1 As range
Dim range2 As range
Dim range3 As range
Dim range4 As range
Dim range5 As range
Dim range6 As range
Dim range7 As range
Dim range8 As range
Dim range9 As range
Dim range10 As range
Dim range11 As range
'If sheet names are different/excluded modify/delete set
'command below and hide command
Set range1 = Sheets("xxx").range("f10:f2000")
Set range2 = Sheets("xxx").range("f10:f2000")
Set range3 = Sheets("xxx").range("f10:f2000")
Set range4 = Sheets("xxx").range("f10:f2000")
Set range5 = Sheets("xxx").range("f10:f2000")
Set range6 = Sheets("xxx").range("f10:f2000")
Set range7 = Sheets("xxx").range("f10:f2000")
Set range8 = Sheets("xxx").range("f10:f2000")
Set range9 = Sheets("xxx").range("f10:f2000")
Set range10 = Sheets("xxx").range("f10:f2000")
Set range11 = Sheets("xxx").range("e26:e160")
For Each c In range1
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range2
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range3
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range4
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range5
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range6
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range7
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range8
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range9
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range10
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
For Each c In range11
If c.Value = "0" Or c.Value = "NA" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate
End Sub