Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Stange numbers | Excel Discussion (Misc queries) | |||
PivotTable Show Properties in Report - stange behaviour | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |