select and delete row based on a cell's value

[pdeshazier]

i'm trying to loop through on column R and if value in Rx is < 600, delete entire row, but i'm getting an error on .ActiveRow.Delete (compile error: invalid or unqualified reference). i'm assuming i need to define something? please see code below. any help is greatly appreciated.

Sub SORTDEPT()
Dim vTxt, vCode
Dim r As Long
Range("R3").Select
While ActiveCell.Value <> "" 'scan thru col R
r = ActiveCell.ROW
vTxt = ActiveCell.Value
If vTxt < 600 Then
.ActiveRow.Delete

End If

ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub

[mancubus]

hi there.

you dont need to select a cell in order to delete its row.

try this:



Sub SORTDEPT()
Dim i As Long

With Worksheets("OPAY")
For i = .Cells(.Rows.Count, "R").End(xlUp).Row To 3 Step -1
If .Cells(i, "R").Value < 600 Then .Rows(i).Delete
Next
End With
End Sub



PS: there is not an "ActiveRow" object, afaik.
PPS: sample workbook does not contain data in column R.

[pdeshazier]

THANKS!! worked perfectly. one more dilemma.... can you tell why this code is returning 'FALSE' in every cell in column S rather than the concatenation of Q value_R value_OPAY?? thanks.
Sub TABNAME() ' TABNAME Macro
'
Dim vCode
Dim r As Long
'
Range("R3").Select
While ActiveCell.Value <> "" 'scan thru col R
r = ActiveCell.ROW
vCode = ActiveCell.FormulaR1C1 = "=RC[-2]&""_""&RC[-1]&""_""&""OPAY"""
Range("S" & r).Value = vCode
'
ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub

[jolivanes]

Is this what you want it to be?

Sub Maybe()
Range("R3", Range("R" & Rows.Count).End(xlUp)).Offset(, 1).Formula = "=RC[-2]&""_""&RC[-1]&""_""&""OPAY"""
End Sub