Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Hi all,
I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Make sure your code is in a General module--not behind a worksheet and not
behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Hi Dave,
The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Are you copying from the formula bar?
If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Hi Dave,
I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Hi Dave,
Essentially, what's happening, in sequence, is the following: - The code is using the worksheet concatenation formula in column AD to combine the values of two other columns. - The code then loops through column AD, copying each cell value, and then using the worksheet find function to locate the value in another sheet. The issue is that when the code copies the cell in column AD and pastes it into the Find menu within Excel, the pasted value has both leading and lagging spaces. When I click within the cell in column AD, however, there are no leading or lagging spaces -- the spaces only show up when the codes pastes the cell value into the Find menu. The code is large as there is a log happening in the worksheet prior to the concatenation of column AD or else I'd paste it in here...I suspect that the fact that the data is pasted from an Excel download from a website has something to do with the issue. Since I have figured out a workaround (deleting columns AD - IV before putting the concatenation formula into AD) and since I'm swamped on getting this project done, I've decided to pass on trying to figure out why this is happening. Thanks, Robert -- Robert "Dave Peterson" wrote: I'm kind of confused at what you're doing (and why the original code didn't work, too!). But maybe... activecell.formular1c1 = someothercell.formular1c1 robs3131 wrote: Hi Dave, I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
First, I'm not sure why or how you're pasting the value into the find dialog.
But why not just use cells.find(what:=somecell.value, ...) Second, if you want to find out what's in that cell, grab this utility from Chip Pearson: http://www.cpearson.com/excel/CellView.aspx robs3131 wrote: Hi Dave, Essentially, what's happening, in sequence, is the following: - The code is using the worksheet concatenation formula in column AD to combine the values of two other columns. - The code then loops through column AD, copying each cell value, and then using the worksheet find function to locate the value in another sheet. The issue is that when the code copies the cell in column AD and pastes it into the Find menu within Excel, the pasted value has both leading and lagging spaces. When I click within the cell in column AD, however, there are no leading or lagging spaces -- the spaces only show up when the codes pastes the cell value into the Find menu. The code is large as there is a log happening in the worksheet prior to the concatenation of column AD or else I'd paste it in here...I suspect that the fact that the data is pasted from an Excel download from a website has something to do with the issue. Since I have figured out a workaround (deleting columns AD - IV before putting the concatenation formula into AD) and since I'm swamped on getting this project done, I've decided to pass on trying to figure out why this is happening. Thanks, Robert -- Robert "Dave Peterson" wrote: I'm kind of confused at what you're doing (and why the original code didn't work, too!). But maybe... activecell.formular1c1 = someothercell.formular1c1 robs3131 wrote: Hi Dave, I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Hi Dave,
Actually I am using "cells.find(what:=A.value, ...), where A is a variable which loops through a range of cells. The issue is that for whatever reason, the cells which A loops through have leading and lagging values that can't be seen when clicking witihin the cell -- the only way I seem to be able to get these spaces to show up is by copying and pasting the values into the Find dialogue box (which I assumed the "cells.find..." code activated). Thanks for the link to the utility - I haven't installed it as I have a workaround to the issue above (and just don't have the time to investigate the root cause), but I will use it in the future as needed. I really appreciate all your help! -- Robert "Dave Peterson" wrote: First, I'm not sure why or how you're pasting the value into the find dialog. But why not just use cells.find(what:=somecell.value, ...) Second, if you want to find out what's in that cell, grab this utility from Chip Pearson: http://www.cpearson.com/excel/CellView.aspx robs3131 wrote: Hi Dave, Essentially, what's happening, in sequence, is the following: - The code is using the worksheet concatenation formula in column AD to combine the values of two other columns. - The code then loops through column AD, copying each cell value, and then using the worksheet find function to locate the value in another sheet. The issue is that when the code copies the cell in column AD and pastes it into the Find menu within Excel, the pasted value has both leading and lagging spaces. When I click within the cell in column AD, however, there are no leading or lagging spaces -- the spaces only show up when the codes pastes the cell value into the Find menu. The code is large as there is a log happening in the worksheet prior to the concatenation of column AD or else I'd paste it in here...I suspect that the fact that the data is pasted from an Excel download from a website has something to do with the issue. Since I have figured out a workaround (deleting columns AD - IV before putting the concatenation formula into AD) and since I'm swamped on getting this project done, I've decided to pass on trying to figure out why this is happening. Thanks, Robert -- Robert "Dave Peterson" wrote: I'm kind of confused at what you're doing (and why the original code didn't work, too!). But maybe... activecell.formular1c1 = someothercell.formular1c1 robs3131 wrote: Hi Dave, I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
I'm not sure where your original data is coming from--but if you're copying from
a web page, you may be "seeing" those HTML non-breaking space characters. David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") robs3131 wrote: Hi Dave, Actually I am using "cells.find(what:=A.value, ...), where A is a variable which loops through a range of cells. The issue is that for whatever reason, the cells which A loops through have leading and lagging values that can't be seen when clicking witihin the cell -- the only way I seem to be able to get these spaces to show up is by copying and pasting the values into the Find dialogue box (which I assumed the "cells.find..." code activated). Thanks for the link to the utility - I haven't installed it as I have a workaround to the issue above (and just don't have the time to investigate the root cause), but I will use it in the future as needed. I really appreciate all your help! -- Robert "Dave Peterson" wrote: First, I'm not sure why or how you're pasting the value into the find dialog. But why not just use cells.find(what:=somecell.value, ...) Second, if you want to find out what's in that cell, grab this utility from Chip Pearson: http://www.cpearson.com/excel/CellView.aspx robs3131 wrote: Hi Dave, Essentially, what's happening, in sequence, is the following: - The code is using the worksheet concatenation formula in column AD to combine the values of two other columns. - The code then loops through column AD, copying each cell value, and then using the worksheet find function to locate the value in another sheet. The issue is that when the code copies the cell in column AD and pastes it into the Find menu within Excel, the pasted value has both leading and lagging spaces. When I click within the cell in column AD, however, there are no leading or lagging spaces -- the spaces only show up when the codes pastes the cell value into the Find menu. The code is large as there is a log happening in the worksheet prior to the concatenation of column AD or else I'd paste it in here...I suspect that the fact that the data is pasted from an Excel download from a website has something to do with the issue. Since I have figured out a workaround (deleting columns AD - IV before putting the concatenation formula into AD) and since I'm swamped on getting this project done, I've decided to pass on trying to figure out why this is happening. Thanks, Robert -- Robert "Dave Peterson" wrote: I'm kind of confused at what you're doing (and why the original code didn't work, too!). But maybe... activecell.formular1c1 = someothercell.formular1c1 robs3131 wrote: Hi Dave, I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Hi Dave,
Yeah, I tried that macro already -- what's strange is that I used the Trimall macro but the spaces were still there...at any rate, I am using a workaround to get around the issue. Thanks! -- Robert "Dave Peterson" wrote: I'm not sure where your original data is coming from--but if you're copying from a web page, you may be "seeing" those HTML non-breaking space characters. David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") robs3131 wrote: Hi Dave, Actually I am using "cells.find(what:=A.value, ...), where A is a variable which loops through a range of cells. The issue is that for whatever reason, the cells which A loops through have leading and lagging values that can't be seen when clicking witihin the cell -- the only way I seem to be able to get these spaces to show up is by copying and pasting the values into the Find dialogue box (which I assumed the "cells.find..." code activated). Thanks for the link to the utility - I haven't installed it as I have a workaround to the issue above (and just don't have the time to investigate the root cause), but I will use it in the future as needed. I really appreciate all your help! -- Robert "Dave Peterson" wrote: First, I'm not sure why or how you're pasting the value into the find dialog. But why not just use cells.find(what:=somecell.value, ...) Second, if you want to find out what's in that cell, grab this utility from Chip Pearson: http://www.cpearson.com/excel/CellView.aspx robs3131 wrote: Hi Dave, Essentially, what's happening, in sequence, is the following: - The code is using the worksheet concatenation formula in column AD to combine the values of two other columns. - The code then loops through column AD, copying each cell value, and then using the worksheet find function to locate the value in another sheet. The issue is that when the code copies the cell in column AD and pastes it into the Find menu within Excel, the pasted value has both leading and lagging spaces. When I click within the cell in column AD, however, there are no leading or lagging spaces -- the spaces only show up when the codes pastes the cell value into the Find menu. The code is large as there is a log happening in the worksheet prior to the concatenation of column AD or else I'd paste it in here...I suspect that the fact that the data is pasted from an Excel download from a website has something to do with the issue. Since I have figured out a workaround (deleting columns AD - IV before putting the concatenation formula into AD) and since I'm swamped on getting this project done, I've decided to pass on trying to figure out why this is happening. Thanks, Robert -- Robert "Dave Peterson" wrote: I'm kind of confused at what you're doing (and why the original code didn't work, too!). But maybe... activecell.formular1c1 = someothercell.formular1c1 robs3131 wrote: Hi Dave, I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue removing leading and lagging spaces
Did you try Chip Pearson's cellview workbook?
I bet that you'd know what was in the cell. robs3131 wrote: Hi Dave, Yeah, I tried that macro already -- what's strange is that I used the Trimall macro but the spaces were still there...at any rate, I am using a workaround to get around the issue. Thanks! -- Robert "Dave Peterson" wrote: I'm not sure where your original data is coming from--but if you're copying from a web page, you may be "seeing" those HTML non-breaking space characters. David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") robs3131 wrote: Hi Dave, Actually I am using "cells.find(what:=A.value, ...), where A is a variable which loops through a range of cells. The issue is that for whatever reason, the cells which A loops through have leading and lagging values that can't be seen when clicking witihin the cell -- the only way I seem to be able to get these spaces to show up is by copying and pasting the values into the Find dialogue box (which I assumed the "cells.find..." code activated). Thanks for the link to the utility - I haven't installed it as I have a workaround to the issue above (and just don't have the time to investigate the root cause), but I will use it in the future as needed. I really appreciate all your help! -- Robert "Dave Peterson" wrote: First, I'm not sure why or how you're pasting the value into the find dialog. But why not just use cells.find(what:=somecell.value, ...) Second, if you want to find out what's in that cell, grab this utility from Chip Pearson: http://www.cpearson.com/excel/CellView.aspx robs3131 wrote: Hi Dave, Essentially, what's happening, in sequence, is the following: - The code is using the worksheet concatenation formula in column AD to combine the values of two other columns. - The code then loops through column AD, copying each cell value, and then using the worksheet find function to locate the value in another sheet. The issue is that when the code copies the cell in column AD and pastes it into the Find menu within Excel, the pasted value has both leading and lagging spaces. When I click within the cell in column AD, however, there are no leading or lagging spaces -- the spaces only show up when the codes pastes the cell value into the Find menu. The code is large as there is a log happening in the worksheet prior to the concatenation of column AD or else I'd paste it in here...I suspect that the fact that the data is pasted from an Excel download from a website has something to do with the issue. Since I have figured out a workaround (deleting columns AD - IV before putting the concatenation formula into AD) and since I'm swamped on getting this project done, I've decided to pass on trying to figure out why this is happening. Thanks, Robert -- Robert "Dave Peterson" wrote: I'm kind of confused at what you're doing (and why the original code didn't work, too!). But maybe... activecell.formular1c1 = someothercell.formular1c1 robs3131 wrote: Hi Dave, I found a workaround to my issue -- if I first delete out column AD before putting in the formula into AD that puts the data into AD, the issue does not come up. Out of curiosity, is there a way to code so that when copying a cell value, the code copies from the formula bar instead of the actual cell? When I record-macro and do this, it just says "Activecell.formula = ...". Thanks for your help! -- Robert "Dave Peterson" wrote: Are you copying from the formula bar? If no, then try that. robs3131 wrote: Hi Dave, The code I wrote below does replace spaces when I actually go in and add a space at the end of a word in a cell, however, it does not replace the leading and lagging spaces that already exist in column AD. I'm starting to wonder if the problem has to do with something else altogether -- when I click into a cell in column AD, there are no leading or lagging spaces to the value in the cell (the cursor blinks directly next to the first and last characters when I click there) -- leading and lagging spaces show up when I copy a cell in column AD and then bring up the "Find" menu by clicking CTRL F and then pasting the value into the "Find What" box...any idea on what would cause this and/or how to resolve this? Thanks! -- Robert "Dave Peterson" wrote: Make sure your code is in a General module--not behind a worksheet and not behind ThisWorkbook. And Sheets("Sheet1").Select range("AD1").entirecolumn.Select looks like it would work to me. But your code looked like it would have gotten rid of all the spaces on the worksheet. What happened when you tried it? robs3131 wrote: Hi all, I'm having a problem removing leading and lagging spaces from cells in a column of data (FYI - the data was input from an Excel spreadsheet that was dowloaded from a website). I looked through some other postings and found the "Trimall" macro -- I've tried this but it is still not removing the spaces. Below is the Trimall macro with two lines that I added (to select all cells in Sheet1) noted with **. Column AD specifically has the data that I am trying to remove the spaces from. I appreciate your help! Sub TrimALL() **Sheets("Sheet1").Select **Cells.Select 'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall ' - Optionally reenable improperly terminated Change Event macros Application.DisplayAlerts = True Application.EnableEvents = True 'should be part of Change Event macro If Application.Calculation = xlCalculationManual Then MsgBox "Calculation was OFF will be turned ON upon completion" End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace What:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(21), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False '--------------------------- Selection.Replace What:=Chr(8), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(9), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next For Each cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) cell.Value = Application.Trim(cell.Value) Next cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Robert -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Leading Spaces | Excel Discussion (Misc queries) | |||
removing leading and trailing spaces | Excel Programming | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Removing leading/trailing spaces | Excel Discussion (Misc queries) | |||
Removing leading spaces | Excel Programming |