Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim data in Excel 2003 | Excel Discussion (Misc queries) | |||
Trim and Keep the Trimmed Data | Excel Worksheet Functions | |||
Trim Data in a Query | Excel Programming | |||
Trim data in charts | Charts and Charting in Excel | |||
VBA Trim and Application.worksheetfunction.Trim | Excel Programming |