VBA to TRIM data <--Rookie here :o)
 

Hello -

I would like to use the Trim function on column C for a worksheet
called "ODBC Updates."

Here is the begining of my code, but I can't figure out how to call
out to this other worksheet and make the Trim command work.

Can anyone please assist me? I've labeled a section 'Trim UPC's on
ODBC Updates' as I think that is where this command should fit.

Thanks! Dan


Sub JCW_Refresh()

' Copy Current Case Price into Prior Case Price
Range("H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

' Refresh the hidden "ODBC Updates" worksheet with current data
Sheets("ODBC Updates").QueryTables(1).Refresh _
BackgroundQuery:=False

' Trim UPC's on ODBC Updates
' (What do I put here??)

' Copy hidden Current Case Price into visible Current Case Price
Range("J5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("H5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

VBA to TRIM data <--Rookie here :o)
 

Hello Dan,

You could create an object reference to a range on the ODBC Updates
worksheet, then use the TRIM function on the cells in it. For example

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Sheets("ODBC Updates").Range("A1:E20")

For Each cell In rng
cell = Trim(cell)

Next cell


HTH,
JP


On Jan 10, 4:52*pm, Dan wrote:
Hello -

I would like to use the Trim function on column C for a worksheet
called "ODBC Updates."

Here is the begining of my code, but I can't figure out how to call
out to this other worksheet and make the Trim command work.

Can anyone please assist me? *I've labeled a section 'Trim UPC's on
ODBC Updates' as I think that is where this command should fit.

Thanks! *Dan

Sub JCW_Refresh()

' Copy Current Case Price into Prior Case Price
* * Range("H5").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.Copy
* * Range("G5").Select
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False

' Refresh the hidden "ODBC Updates" worksheet with current data
* * Sheets("ODBC Updates").QueryTables(1).Refresh _
* * BackgroundQuery:=False

' Trim UPC's on ODBC Updates
' (What do I put here??)

' Copy hidden Current Case Price into visible Current Case Price
* * Range("J5").Select
* * Range(Selection, Selection.End(xlDown)).Select
* * Selection.Copy
* * Range("H5").Select
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False

VBA to TRIM data <--Rookie here :o)
 

I guess it should be

Set rng = Sheets("ODBC Updates").Range("C1:C100")

because you mentioned it was for column C. :-)

(adjust range to suit your needs)

HTH,
JP

On Jan 10, 4:59*pm, JP wrote:
Hello Dan,

You could create an object reference to a range on the ODBC Updates
worksheet, then use the TRIM function on the cells in it. For example

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Sheets("ODBC Updates").Range("A1:E20")

For Each cell In rng
cell = Trim(cell)

Next cell

HTH,
JP

VBA to TRIM data <--Rookie here :o)
 

For Each cell In rng
cell = Trim(cell)
Next cell

You need to be very careful with that code because it can wipe out formulas,
replacing a formula with a value. Also, it would be good practice to turn
off events:

On Error GoTo ErrH:
Application.EnableEvents = False
For Each cell In rng
If cell.HasFormula = False Then
cell = Trim(cell)
End If
Next cell
ErrH:
Application.EnableEvents = True

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)



"JP" wrote in message
...
Hello Dan,

You could create an object reference to a range on the ODBC Updates
worksheet, then use the TRIM function on the cells in it. For example

Dim rng As Excel.Range
Dim cell As Excel.Range

Set rng = Sheets("ODBC Updates").Range("A1:E20")

For Each cell In rng
cell = Trim(cell)

Next cell


HTH,
JP


On Jan 10, 4:52 pm, Dan wrote:
Hello -

I would like to use the Trim function on column C for a worksheet
called "ODBC Updates."

Here is the begining of my code, but I can't figure out how to call
out to this other worksheet and make the Trim command work.

Can anyone please assist me? I've labeled a section 'Trim UPC's on
ODBC Updates' as I think that is where this command should fit.

Thanks! Dan

Sub JCW_Refresh()

' Copy Current Case Price into Prior Case Price
Range("H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

' Refresh the hidden "ODBC Updates" worksheet with current data
Sheets("ODBC Updates").QueryTables(1).Refresh _
BackgroundQuery:=False

' Trim UPC's on ODBC Updates
' (What do I put here??)

' Copy hidden Current Case Price into visible Current Case Price
Range("J5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("H5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

VBA to TRIM data <--Rookie here :o)
 

Noted. Thank you!

--JP

On Jan 10, 5:14*pm, "Chip Pearson" wrote:

For Each cell In rng
* cell = Trim(cell)
*Next cell

You need to be very careful with that code because it can wipe out formulas,
replacing a formula with a value. *Also, it would be good practice to turn
off events:

On Error GoTo ErrH:
Application.EnableEvents = False
For Each cell In rng
* * If cell.HasFormula = False Then
* * * * cell = *Trim(cell)
* * End If
Next cell
ErrH:
Application.EnableEvents = True

VBA to TRIM data <--Rookie here :o)
 

On Jan 10, 2:29*pm, JP wrote:
Noted. Thank you!

--JP

On Jan 10, 5:14*pm, "Chip Pearson" wrote:



For Each cell In rng
* cell = Trim(cell)
*Next cell

You need to be very careful with that code because it can wipe out formulas,
replacing a formula with a value. *Also, it would be good practice to turn
off events:

On Error GoTo ErrH:
Application.EnableEvents = False
For Each cell In rng
* * If cell.HasFormula = False Then
* * * * cell = *Trim(cell)
* * End If
Next cell
ErrH:
Application.EnableEvents = True- Hide quoted text -

- Show quoted text -

Thanks for the assistance. Another question please...some of the
cells in this range start with a 0, ie 0345681. After trying the
suggested code, it is removing the leading zeros. How can these cells
be treated as text so the zero stays in tact?

Regards,
Dan