Deleting Rows 3

[cclgroup]

Hi,

I'm having trouble deleting rows based on a certian criteria. I have two sheets in workbook. One has a list of all the diferent criteria I need and the other sheet has a tonnes of rows of data. I want to delete all the rows which don't have the crietia from the first sheet. The criteria should match Coloumn A in sheet2. Thanks in advance for the help!

 

[JerryKlmns]

Build a function that uses all criteria.
Loop the rows and check that function on every row.
Delete the row if function returns true.

When you reach a dead end, post your code where you have trouble and clearly state all details.

 

[cclgroup]

Sub delete()
Sheets("866").Select
Dim fee1 As Integer
Dim fee2 As Integer
Dim fee3 As Integer
Dim fee4 As Integer
Dim fee5 As Integer
Dim c As Range

fee1 = Sheets("Sheet1").Range("a1").Value
fee1 = Sheets("Sheet1").Range("a2").Value
fee1 = Sheets("Sheet1").Range("a3").Value
fee1 = Sheets("Sheet1").Range("a4").Value


For Each c In Range("A:A")

If c.Value <> fee1 Then
ElseIf c.Value <> fee2 Then
ElseIf c.Value <> fee3 Then
ElseIf c.Value <> fee4 Then
ElseIf c.Value <> fee5 Then
Selection.delete Shift:=xlUp
End If

Next c

End Sub

 

[cclgroup]

So this is what i have so far...im not sure if it makes sense where i'm stuck is actually deleting the line that doesn't meet the criteria....

 

[zahead31]

Couple things:

You are only assigning a value to fee1. fee2... fee5 do not appear to be assigned a value, but they are conditions in the If statements.

Also, I could be missing something, but the only selection I see you make is Sheet "866". Then your code attempts to delet the selection. From what I can see, you are trying to delete a worksheet and not a row.

Hope this helps,
Matt

 

[xlbo]

For starters, all deletion should be done from the bottom up so:

lRow = cells(65536,1).end(xlup).row

For i = lRow to 1 step -1

 if cells(i,1).value = criteria then
    rows(i).entirerow.delete
 end if

Next i

would be your basic loop

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JerryKlmns]

If c.Value <> fee1 Then
ElseIf c.Value <> fee2 Then
ElseIf c.Value <> fee3 Then
ElseIf c.Value <> fee4 Then
ElseIf c.Value <> fee5 Then
Selection.delete Shift:=xlUp
End If

Let's format it, to be read nice and easy:

If c.Value <> fee1 Then
  'do nothing
ElseIf c.Value <> fee2 Then
   'do nothing
   ElseIf c.Value <> fee3 Then
   'do nothing
      ElseIf c.Value <> fee4 Then
      'do nothing
         ElseIf c.Value <> fee5 Then
            Selection.delete Shift:=xlUp
End If

Let's interpret it into words

If c.Value is equal to fee1 and
c.Value is equal to fee2 and
c.Value is equal to fee3 and
c.Value is equal to fee4 and
c.Value is equal to fee5

means that c.Value equals all fees which happens only when all fees are equal.

Were you checking for c.Value to equal one of these fees?
Then your function should be

If c.Value = fee1 OR _
   c.Value = fee2 OR _
   c.Value = fee3 OR _
   c.Value = fee4 OR _
   c.Value = fee5 Then
      Selection.delete Shift:=xlUp
End If

Or using a different crayon color

If cells(i,1).value = fee1 OR _
   cells(i,1).value = fee2 OR _
   cells(i,1).value = fee3 OR _
   cells(i,1).value = fee4 OR _
   cells(i,1).value = fee5 Then
      rows(i).entirerow.delete
End If

And a tip from onother crayon color: avoid using Selection in your code. [I think that was a white one that xlbo used, that's why wasn't visible to all of us the first time!]

 

[Rofeu]

Actually, only Fee1 is assigned a value.

anyway, why not:

 Select Case C.Value
    Case Fee1, Fee2, Fee3, Fee4, Fee5
        'Do nothing
    Case Else
        C.EntireRow.Delete
End Select

Cheers,

Roel