I don't want to sort my data at all...

sebastienm, I recieve an error that says no cells were found.



"sebastienm" wrote:

Hi,
'---------------------------------------------------------------------
Sub test()
Dim RgToSearch As Range, cell As Range
Dim rg As Range, rg1 As Range, rg2 As Range

Set RgToSearch = Range("A:A") '<***************** change here

'quickly shortern the range to numbers only (no text...)
'This section cAN BE REMOVED IF NOT APPLICABLE
Set rg1 = RgToSearch.SpecialCells(xlCellTypeConstants, 1)
Set rg2 = RgToSearch.SpecialCells(xlCellTypeFormulas, 1)
If rg1 Is Nothing Then
Set rg = rg2
ElseIf rg2 Is Nothing Then
Set rg = rg1
Else 'none is nothing
Set rg = Application.Union(rg1, rg2)
End If

If rg Is Nothing Then Exit Sub

'find negative numbers cells and put them in rg2
Set rg2 = Nothing
For Each rg1 In rg.Cells
If rg1.Value < 0 Then 'condition here
If rg2 Is Nothing Then 'add cell to range
Set rg2 = rg1
Else
Set rg2 = Application.Union(rg1, rg2)
End If
End If
Next

'process the delete
If Not rg2 Is Nothing Then rg2.EntireRow.Delete

End Sub
'------------------------------------------------------------------------
--
Regards,
Sébastien


"John" wrote:

Can this piece of code be adapted easily to delete a row when the active
cell value is less than 0?

Range("h6:h700").Select
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns(3)

Set rngFound = rngToSearch.Find("-")
If rngFound Is Nothing Then
MsgBox "No Deletions Found"
Else
Do
rngFound.EntireRow.Delete

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub