VBA For each row in visible selection

[Drac0]

I have wrote function that will change few cells for each row in selection. Lets assume function will set text in few cells for each row.
If there is no filter and i have just selected 1 cell function runs OK
if there is no filter and i have selected more than 1 cell/row its OK
If there is filter and i have selected more than 1 cell/row its OK

**if there is Filter and i have just selected 1 cell funtions goes wrong**

In my file i have header, i set filter on some field for example column 1 to look for all "Sales" and only for those i would like to change "Sales" to "Buys"
However my function change even header then goes for all selected rows and at the end it runs till the end of worksheet.

Here is my code


Sub ChangeText(control as IRibbonControl)
dim sRange as Range
dim sSelect as Variant
set sSelect = Selection.SpecialCells(xlCellTypeVisible)

for each sRange in sSelect.Rows
Select Case Cells(sRange.Row, 3)
Case "Sales"
Cells(sRange.Row,3) = 'Buys"
case else
Cells(sRange.Row,3) = "Internal"
End Select
Next
End Sub

This is just sample, in main code i have multiple cases
what i figured out is that Selection.SpecialCells(xlCellTypeVisible).Areas.Co unt returns 2 when i filter data and just select 1 cell. Could you please advise me on this?

[Admin]

Hi

Welcome to board!

not sure, but does this make any good ?


Dim r As Range

Set sSelect = Selection.SpecialCells(xlCellTypeConstants)

For Each sRange In sSelect.Areas
For Each r In sRange.Rows
Select Case Cells(r.Row, 3)
Case "Sales"
Cells(r.Row, 3) = "Buys"
Case Else
Cells(r.Row, 3) = "Internal"
End Select
Next
Next