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

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

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