Microsoft Office Forums - View Single Post

gbaker · 07-18-2014, 11:12 AM

Hi Joli,

I put it at the end of the code and go an error. It didn't remove the rows in any of the worksheets.

.Range(.Cells(1, 1), .Cells(lr, lc)).AutoFilter 4, "<11"

Thanks for trying. I'll keep working on it. I did find another way to do it. Probably not the best way. I set up conditional formulas on each sheet that highlights anything in Yellow if the Age column is less than 11.

Then I ran this code on one sheet that I found on the net:

Code:

Sub DeleteRowslessthan10()
Dim rg As Range
Dim i As Long
On Error Resume Next
Set rg = Application.InputBox("Please select a single column range of cells." & vbLf & _
        "If value is Less than 10,that row will be deleted.", _
        Default:="D2:D3000", Type:=8)
On Error GoTo 0

Application.ScreenUpdating = False
If Not rg Is Nothing Then
    Set rg = Intersect(rg, ActiveSheet.UsedRange)
    If Not rg Is Nothing Then
        For i = rg.Rows.Count To 1 Step -1
            If IsError(rg.Cells(i, 1).Value) Then
                rg.Rows(i).EntireRow.Delete
            ElseIf UCase(rg.Cells(i, 1).Value) = "N/A" Then
                rg.Rows(i).EntireRow.Delete
            ElseIf rg.Cells(i, 1).Value = "" Then
            ElseIf IsNumeric(rg.Cells(i, 1)) Then
                If rg.Cells(i, 1).Value < 11 Then rg.Rows(i).EntireRow.Delete
            End If
        Next
    End If
End If
End Sub

It works but I haven't tested it on all worksheets. I might try to use the Call feature. The only thing I don't like about it is it makes the user choose the column with a InputBox and I can't figure out how to change that so it just runs the code based on Coulumn 4.

Thoughts ?

Thanks in Advance
GBaker