Log in

View Full Version : How to modify only visible cells



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

Jacob Hilderbrand
04-26-2007, 08:55 PM
If Selection.Cells.Count = 1 Then

Else

End If

dpawley
04-28-2007, 08:01 AM
thanks DRJ for the relpy but I had already suggested that solution.

I believe I have found a better solution although still involves an IF...Then statement. Although the code is somewhat more obvious what you are doing.


Sub ReplaceComma()
comma = Chr(44)
commaspace = comma + Space(1)
For Each cel In Selection
If Not (cel.EntireRow.Hidden) Then
cel.Value = Replace(Replace(cel, commaspace, Space(1)), comma, Space(1))
Else
End If
Next cel
End Sub

Jacob Hilderbrand
04-29-2007, 03:50 PM
Well in your example you will check every cell in your selection. In my example you would only make the check once, in yours it could be 1000s of times.

Performance wise it probably would not matter either way (unless your range was really large), but just something to consider.

dpawley
04-29-2007, 08:14 PM
Good point DRJ! It seems neither solution is quite what it should be.

If you select a range of cells in a column of a filtered spreadsheet and click the Bold toolbar button, only the visible cells are modied. This is exactly what I want and both of the examples above get the job done and DRJ's is the most efficient. Although, it seems strange that the For Each cel in Selection works even for a selection of 1 cell and does not work if the selection is for only 1 cell if .SpecialCells(xlCellTypeVisible) option is present.

Thanks again for your advice DRJ.

Jacob Hilderbrand
05-05-2007, 12:03 PM
You may like this approach better:


For Each Cel In Intersect(Selection, Selection.SpecialCells(xlCellTypeVisible))