Select only visible cell via input box

[Rolly_Sefu]

Hello I am working on code but got stuck.

[Sub Test_SelRangePaste()
Dim UserRange As Range
'On Error GoTo Canceled
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
For Each cell In UserRange
    y = cell.Address(False, False)
    x = Replace(cell, "-", "") * (-1)
    Range(y) = x
Next cell
Exit Sub
Canceled:
End Sub
/CODE]

I have range of around 500 row witch have a minus ( - ) end.

So I have made this code to replace the minus from the end and transform the number to negative.

So far so good.

The problem is that not all cell in the 500 range has the minus so I have filtered the column for the minus.

And when I run the code and select 2 cells one above the other I have ex: $H$193:$H$198 and when the code runs it looks into H194 as well, but that is not necessary.
(and did not want to slow the code down looking into cells that is not necessary)

is there a way to select only the visible cell (after filter is applied) into the input box ? or the "For' to look only at visible cells ?

+ if the input box gets an OK click without any cell to be selected how can I send a msgbox that no input has been added, because now when I click OK I get a error message.

Thank you.

 

[pbornemeier]

The Data | Text to Columns menu function will convert numbers with a trailing minus to negative numbers. The option is in step 3 of the wizard under the Advanced button.

Option Explicit

Sub Test_SelRangePaste()
    Dim UserRange As Range
    Dim rngCell As Range
    Dim y As String
    Dim x As Variant
    'On Error GoTo Canceled
    Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
    For Each rngCell In UserRange[COLOR="#FF0000"].SpecialCells(xlCellTypeVisible)[/COLOR]
        y = rngCell.Address(False, False)
        x = Replace(rngCell, "-", "") * (-1)
        Range(y) = x
    Next rngCell
    Exit Sub
Canceled:
End Sub

 

[Rolly_Sefu]

Hy, thanks for the update, the ".SpecialCells(xlCellTypeVisible)" works perfectly.

Unfortunately I can not use the text to column since some of the cell have spaces before the number and by multiplying it with -1 I automatically lose the spaces.

Thanks.