genracela
06-29-2010, 01:17 AM
I'm wondering if I there's a shorter way to write this code.
I have 4 tabs/sheets to work on so I used "ThisWorkbook" instead of "ActiveSheet" to delete all rows that has the following criterias in G column, in all my 4 tabs.
Option Explicit
Sub RowNCFT()
With ThisWorkbook
Dim lLastRow As Long
Dim i As Long
lLastRow = Cells(Rows.Count, "G").End(xlUp).Row
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "ACCESSORIES" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "FOOTWEAR" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "HOUSEWARES" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "INNERWEAR" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "ENTERTAINMENT & LEISURE" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "GIFT & DECORATIVE" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "JEWELRY" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "KIDS" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "NUTRITION" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "OUTERWEAR" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "SALESTOOLS" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "UNKNOWN" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "WATCHES" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
End With
End Sub
Thanks!!!
I have 4 tabs/sheets to work on so I used "ThisWorkbook" instead of "ActiveSheet" to delete all rows that has the following criterias in G column, in all my 4 tabs.
Option Explicit
Sub RowNCFT()
With ThisWorkbook
Dim lLastRow As Long
Dim i As Long
lLastRow = Cells(Rows.Count, "G").End(xlUp).Row
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "ACCESSORIES" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "FOOTWEAR" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "HOUSEWARES" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "INNERWEAR" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "ENTERTAINMENT & LEISURE" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "GIFT & DECORATIVE" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "JEWELRY" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "KIDS" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "NUTRITION" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "OUTERWEAR" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "SALESTOOLS" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "UNKNOWN" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value = "WATCHES" Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
End With
End Sub
Thanks!!!