Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA tweak to run faster? <--Rookie here
Hello -
I have the following code which is processing very slowly. Ever since I added in the TRIM section, it has slowed way down. FYI...the amount of active cells in column C change, so I just used a default number of 1000, but this can be changed if needed. Thanks for any ideas! Dan Sub ODBC_Refresh() ' Refresh the hidden "ODBC Updates" worksheet with current data Sheets("ODBC Updates").QueryTables(1).Refresh _ BackgroundQuery:=False ' Format UPC column into Text Columns("C:C").NumberFormat = "@" ' Trim UPC's on ODBC Updates Dim rng As Excel.Range Dim cell As Excel.Range Set rng = Sheets("ODBC Updates").Range("C2:C1000") For Each cell In rng cell = Trim(cell) Next cell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA tweak to run faster? <--Rookie here
Turn off calculations, screen updating and if there is change event code then
turn off events... Something like this... Sub ODBC_Refresh() ' Refresh the hidden "ODBC Updates" worksheet with current data Sheets("ODBC Updates").QueryTables(1).Refresh _ BackgroundQuery:=False ' Format UPC column into Text Columns("C:C").NumberFormat = "@" ' Trim UPC's on ODBC Updates Dim rng As Excel.Range Dim cell As Excel.Range Set rng = Sheets("ODBC Updates").Range("C2:C1000") With Application .EnableEvents = False .ScreenUpdating = False .Calculation = xlCalculationManual For Each cell In rng cell = Trim(cell) Next cell .EnableEvents = True .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With End Sub -- HTH... Jim Thomlinson "Dan" wrote: Hello - I have the following code which is processing very slowly. Ever since I added in the TRIM section, it has slowed way down. FYI...the amount of active cells in column C change, so I just used a default number of 1000, but this can be changed if needed. Thanks for any ideas! Dan Sub ODBC_Refresh() ' Refresh the hidden "ODBC Updates" worksheet with current data Sheets("ODBC Updates").QueryTables(1).Refresh _ BackgroundQuery:=False ' Format UPC column into Text Columns("C:C").NumberFormat = "@" ' Trim UPC's on ODBC Updates Dim rng As Excel.Range Dim cell As Excel.Range Set rng = Sheets("ODBC Updates").Range("C2:C1000") For Each cell In rng cell = Trim(cell) Next cell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA tweak to run faster? <--Rookie here
On Jan 10, 8:52*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Turn off calculations, screen updating and if there is change event code then turn off events... Something like this... Sub ODBC_Refresh() ' Refresh the hidden "ODBC Updates" worksheet with current data * * Sheets("ODBC Updates").QueryTables(1).Refresh _ * * BackgroundQuery:=False ' Format UPC column into Text * * Columns("C:C").NumberFormat = "@" ' Trim UPC's on ODBC Updates * * Dim rng As Excel.Range * * Dim cell As Excel.Range * * Set rng = Sheets("ODBC Updates").Range("C2:C1000") * * With Application * * .EnableEvents = False * * .ScreenUpdating = False * * .Calculation = xlCalculationManual * * For Each cell In rng * * cell = Trim(cell) * * Next cell * * .EnableEvents = True * * .ScreenUpdating = True * * .Calculation = xlCalculationAutomatic * * End With End Sub -- HTH... Jim Thomlinson "Dan" wrote: Hello - I have the following code which is processing very slowly. *Ever since I added in the TRIM section, it has slowed way down. FYI...the amount of active cells in column C change, so I just used a default number of 1000, but this can be changed if needed. Thanks for any ideas! *Dan Sub ODBC_Refresh() ' Refresh the hidden "ODBC Updates" worksheet with current data * * Sheets("ODBC Updates").QueryTables(1).Refresh _ * * BackgroundQuery:=False ' Format UPC column into Text * * Columns("C:C").NumberFormat = "@" ' Trim UPC's on ODBC Updates * * Dim rng As Excel.Range * * Dim cell As Excel.Range * * Set rng = Sheets("ODBC Updates").Range("C2:C1000") * * For Each cell In rng * * cell = Trim(cell) * * Next cell End Sub- Hide quoted text - - Show quoted text - Thanks so much Jim, that totally rocked! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need someone to help tweak a code | Excel Discussion (Misc queries) | |||
Array Help Tweak | Excel Worksheet Functions | |||
Code Tweak | Excel Programming | |||
Can someone please tweak my Macro? | Excel Discussion (Misc queries) | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) |