Macro deleting rows based in main sheet on values in another sheet

  • Thread starter Thread starter n m B
  • Start date Start date
N

n m B

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
 
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
Click to expand...

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
 
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– Skjul sitert tekst –

– Vis sitert tekst –
Click to expand...

Thanks
but I'm struggling on this one ....
 
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
Click to expand...

If you are saying you want delete all rows in delete where the value in colc matches cell cx then it seems that a simple data>filter>autofilter>filter col c for the value>delete. Record a macro while doing and then clean it up. If that doesn't work send file to dguillett1@gmail with examples.
 
Don, Please tell how he will match values with sheet("delete") using autofilter (it seems there are multiple values)? Then I assume you delete visible cells only?
 
Don, Please tell how he will match values with sheet("delete") using autofilter (it seems there are multiple values)? Then I assume you delete visible cells only?
Click to expand...

I thought I qualified my answer "look in col C" It seems that the ball is in the OP's court.
 
Back
Top