On Jun 11, 9:27*am, n m B wrote:
Hey guys

I have a sheet REGISTER as main sheet.
In this sheet there is cellvalues in column C which i want to check.
If the value in celle Cx (sheet *REGISTER) are to be fond as any value
in the sheet DELETE columnd C (except row 1:1 in both sheets=header)
this row in sheet REGISTER will be deleted.

I am capabel to delete rows with one particular value, but dont know
how to delete all rows checking a delete-reference table

Example of Register sheet:
Col A * * * * * Col B * * * * * * *Col C
Col D * * * * *Col E
ÅrAns *AnsHÅr * * ÅrUkePrd * Uke-Prd Ansatt-År-Prd
2012-74 74-2012-2 * * * * * *2012-29-1 *291 * * 74-2012-291
2012-540 * * * *540-2012-2 * * 2012-29-1 * * * *291 * * 540-2012-291
2012-8029 * * * 8029-2012-2 * 2012-41-2 412 * * 8029-2012-412
2012-189 * * * *189-2012-2 * * 2012-41-2 * * * *412 * * 189-2012-412
2012-301 * * * *301-2012-2 * * 2012-41-2 * * * *412 * * 301-2012-412
2012-8029 * * * 8029-2012-2 * 2012-43-2 432 * * 8029-2012-432
2012-900 * * * *900-2012-2 * * 2012-43-2 * * * *432 * * 900-2012-432
2012-301 * * * *301-2012-2 * * 2012-43-2 * * * *432 * * 301-2012-432
2012-301 * * * *301-2012-2 * * 2012-50-2 * * * *502 * * 301-2012-502

Example of Delete sheet:
Col A * * * * * Col B * * * * * * *Col C
Col D * * * * *Col E
ÅrAns *AnsHÅr * * ÅrUkePrd * * * * * * * * * * *Uke-Prd * * * *Ansatt-År-Prd
2012-900 * * * *900-2012-1 * * 2012-21-2 * * * *212 * * 900-2012-212
2012-346 * * * *346-2012-1 * * 2012-17-2 * * * *172 * * 346-2012-172
2012-67 67-2012-1 * * * * * *2012-19-2 *192 * * 67-2012-192
2012-1250 * * * 1250-2012-1 * 2012-23-2 232 * * 1250-2012-232
2012-996 * * * *996-2012-1 * * 2012-22-2 * * * *222 * * 996-2012-222
2012-74 74-2012-1 * * * * * * 2012-20-2 202 * * 74-2012-202
2012-149 * * * *149-2012-1 * * 2012-18-2 * * * *182 * * 149-2012-182
2012-92 92-2012-2 * * * * * * 2012-27-1 271 * * 92-2012-271
2012-572 * * * *572-2012-2 * * 2012-47-2 * * * *472 * * 572-2012-472
2012-160 * * * *160-2012-2 * * 2012-39-2 * * * *392 * * 160-2012-392
2012-197 * * * *197-2012-2 * * 2012-30-1 * * * *301 * * 197-2012-301
2012-826 * * * *826-2012-2 * * 2012-34-2 * * * *342 * * 826-2012-342
2012-171 * * * *171-2012-2 * * 2012-35-2 * * * *352 * * 171-2012-352
2012-1076 * * * 1076-2012-2 * *2012-36-2 * * * *362 * * 1076-2012-362
2012-8062 * * * 8062-2012-2 * *2012-49-2 * * * *492 * * 8062-2012-492
2012-894 * * * *894-2012-2 * * *2012-31-1 * * * 311 * * 894-2012-311
2012-402 * * * *402-2012-2 * * *2012-28-1 * * * 281 * * 402-2012-281
2012-65 65-2012-2 * * * * * * 2012-37-2 372 * * 65-2012-372
2012-176 * * * *176-2012-2 * * 2012-26-1 * * * *261 * * 176-2012-261
2012-262 * * * *262-2012-2 * * 2012-44-2 * * * *442 * * 262-2012-442
2012-169 * * * *169-2012-2 * * 2012-38-2 * * * *382 * * 169-2012-382
2012-86 86-2012-2 * * * * * * 2012-52-2 522 * * 86-2012-522
2012-8068 * * * 8068-2012-2 * 2012-51-2 512 * * 8068-2012-512

I can offer some psuedo-code:

* loop over all values in column C on the DELETE sheet and add each
unique value to an array or Scripting.Dictionary
* now loop over all values in column C on the REGISTER sheet and test
for existence in your array or dictionary - if exists then add to a
range for deletion
* once complete then delete all the rows in the range for deletion -
you need to do this last to prevent your looping from problems if you
delete as you go

and code - I like a simple life so I use Scripting.Dictionary instead
of arrays:

Option Explicit

Sub DoIt()
' the values we will delete on:
Dim dicDELETE As Scripting.Dictionary
Set dicDELETE = New Scripting.Dictionary

' collect the values to delete on:
With ThisWorkbook.Sheets("DELETE")
Dim rC As Range
Set rC = .Cells(2, 3)
Do While rC.Value < vbNullString
If Not dicDELETE.Exists(rC.Value) Then dicDELETE.Add
rC.Value, rC.Row
Set rC = rC.Offset(1, 0)
Loop
End With

' walk over the REGISTER and collect the rows for deletion:
With ThisWorkbook.Sheets("REGISTER")
Dim rRowsToDelete As Range
Set rRowsToDelete = Nothing
Set rC = .Cells(2, 3)
Do While rC.Value < vbNullString
If dicDELETE.Exists(rC.Value) Then
' we want to delete this row:
If rRowsToDelete Is Nothing Then
Set rRowsToDelete = rC.EntireRow
Else
Set rRowsToDelete =
Application.Union(rRowsToDelete, rC.EntireRow)
End If
End If
Set rC = rC.Offset(1, 0)
Loop
' actual delete:
If Not rRowsToDelete Is Nothing Then
rRowsToDelete.EntireRow.Delete
End With
End Sub



Chrisso