dpawley
04-26-2007, 08:36 PM
I am working in a worksheet which has been autofiltered. I would like to be able to select 1 or more cells in a column and run some code that would remove commas and/or commas+space. I have this code which works except if I select only 1 cell, whereby all visible cells on the worksheet (ie all columns) instead of just the 1 cell I have selected.
Sub ReplaceComma()
comma = Chr(44)
commaspace = comma + Space(1)
For Each cel In Selection.SpecialCells(xlCellTypeVisible)
cel.Value = Replace(Replace(cel, commaspace, Space(1)), comma, Space(1))
Next cel
End Sub
If I remove the .SpecialCells(xlCellTypeVisible) in the For statement I can select 1 or more cells and the code works except of course all cells between the selection are modified including those not visible when selecting more than 1 cell in the autofiltered worksheet.
I could count the cells in the selection and put an IF Then statement in but it seems like there should be an easier way.... Odd when I do count with selection.count and I only have 1 cell selected with .SpecialCells(xlCellTypeVisible) option, I get all cells that are visible instead of the 1.
Any suggestions?
thanks, Dave
Sub ReplaceComma()
comma = Chr(44)
commaspace = comma + Space(1)
For Each cel In Selection.SpecialCells(xlCellTypeVisible)
cel.Value = Replace(Replace(cel, commaspace, Space(1)), comma, Space(1))
Next cel
End Sub
If I remove the .SpecialCells(xlCellTypeVisible) in the For statement I can select 1 or more cells and the code works except of course all cells between the selection are modified including those not visible when selecting more than 1 cell in the autofiltered worksheet.
I could count the cells in the selection and put an IF Then statement in but it seems like there should be an easier way.... Odd when I do count with selection.count and I only have 1 cell selected with .SpecialCells(xlCellTypeVisible) option, I get all cells that are visible instead of the 1.
Any suggestions?
thanks, Dave