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