WTF?? Stange happings with a VB code
 

Ok, I'm using the below VB code and to my surprise, none of my formulas work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With

WTF?? Stange happings with a VB code
 

Perhaps
.Calculation = xlCalculationManual

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pgarcia" wrote in message
...
Ok, I'm using the below VB code and to my surprise, none of my formulas
work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With

WTF?? Stange happings with a VB code
 

You must set calculation back

See the complete code here
http://www.rondebruin.nl/delete.htm#AutoFilter


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message ...
Ok, I'm using the below VB code and to my surprise, none of my formulas work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With

WTF?? Stange happings with a VB code
 

Application.Calculation = xlCalculationManual

This is telling Excel to stop updating formulas untill you change it back to:
Application.Calculation = xlCalculationAutomatic
or hit F9

"Don Guillett" wrote:

Perhaps
.Calculation = xlCalculationManual

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pgarcia" wrote in message
...
Ok, I'm using the below VB code and to my surprise, none of my formulas
work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With

WTF?? Stange happings with a VB code
 

Ah, and that's what I get for not taking a class. When I added the following
it work fine. Thanks Ron.

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

"Ron de Bruin" wrote:

You must set calculation back

See the complete code here
http://www.rondebruin.nl/delete.htm#AutoFilter


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message ...
Ok, I'm using the below VB code and to my surprise, none of my formulas work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With

WTF?? Stange happings with a VB code
 

Probably best to use

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculation = calcmode
End With

so as to ensure the sheet gets recalculated in case it was originally manual


--
__________________________________
HTH

Bob

"pgarcia" wrote in message
...
Ah, and that's what I get for not taking a class. When I added the
following
it work fine. Thanks Ron.

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

"Ron de Bruin" wrote:

You must set calculation back

See the complete code here
http://www.rondebruin.nl/delete.htm#AutoFilter


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message
...
Ok, I'm using the below VB code and to my surprise, none of my formulas
work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With

WTF?? Stange happings with a VB code
 

Or just do the calculation:

With Application
.ScreenUpdating = True
.Calculate
.Calculation = calcmode
End With

Bob Phillips wrote:

Probably best to use

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculation = calcmode
End With

so as to ensure the sheet gets recalculated in case it was originally manual

--
__________________________________
HTH

Bob

"pgarcia" wrote in message
...
Ah, and that's what I get for not taking a class. When I added the
following
it work fine. Thanks Ron.

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

"Ron de Bruin" wrote:

You must set calculation back

See the complete code here
http://www.rondebruin.nl/delete.htm#AutoFilter


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message
...
Ok, I'm using the below VB code and to my surprise, none of my formulas
work.
Can any see why this is happing? When I remove the code, formulas work.
Strange.

Does it have to do with "With Application"?

Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
DeleteValue = "Closed"
With ActiveSheet
.AutoFilterMode = False
.Range("AE1:AE" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With



--

Dave Peterson