Delete rows based on content in list

[TorrO]

Hi

I have a ledger with rows i need to delete based on a list.
If it's s possibele to do without a makro it would be perfect.

Case:
If a spesific cell in a row equals a number in the selecton list then delete all row.

Selection list with accounts that shal be deleted from ledger:
1920
5000
5010

Ledger BEFORE
1000, 1900, lunsj, 100
1001, 1920, car, 1000
1002, 1420, garbage, 1555
1003, 5000, vage, 1200
1004, 1900, food, 200
etc
etc
etc
1000's of lines

Result:

Ledger AFTER
1000, 1900, lunsj, 100
1002, 1420, garbage, 1555
1004, 1900, food, 200


Thanks

 

[SteveO59L]

so

1000, 1900, lunsj, 100

is an entry in one cell?

 

[frank_AL]

TorrO,

You could add a column and use something like the COUNTA Function to find Rows with those values, then sort on that column and delete any row with a value greater that 0. However, it would be a very simple macro to find those rows with those values and delete them without need to add a column and manipulate your data in the manner described above.

What column or columns are the Ledger values in that would need to be checked for those values?
Is there a Header row? If so, what row is the first row of data to be checked for those values?
What is the name of the Worksheet to be evaluated?

Answer those questions and I'll be glad to provide you a solution.

Frank_al

 

[TorrO]

so

1000, 1900, lunsj, 100

is an entry in one cell?

commas separate cells, each entry in single cell, and in colums A, B, C, D.

A B C D
1000 1900 lunsj 100

 

[TorrO]

Hi

thanks for answer.

Seems that i need a macro. My plan is that this deleting is automatic.

 

[SteveO59L]

something along the lines of (assuming the list of values to check are in a1:a3, and your list of data starts row 11)

Dim c1 As Range, rng1
Dim lr As Long
Dim chk_val As String
Dim w As Long

lr = Range("a" & Rows.Count).End(xlUp).Row

Set rng1 = Range("a1:a3")
For Each c1 In rng1
chk_val = c1.Value
For w = lr To 11 Step -1
If Cells(w, "A").Value = chk_val Then
Cells(w, "A").EntireRow.Delete
End If
Next w
For w = lr To 11 Step -1
If Cells(w, "b").Value = chk_val Then
Cells(w, "b").EntireRow.Delete
End If
Next w
Next c1