ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro deleting rows based in main sheet on values in another sheet (https://www.excelbanter.com/excel-programming/446294-macro-deleting-rows-based-main-sheet-values-another-sheet.html)

n m B
Macro deleting rows based in main sheet on values in another sheet
 
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

Chrisso
Macro deleting rows based in main sheet on values in another sheet
 
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

n m B
Macro deleting rows based in main sheet on values in another sheet
 
On 11 Jun, 11:43, Chrisso wrote:
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– Skjul sitert tekst –

– Vis sitert tekst –

Thanks
but I'm struggling on this one ....

Don Guillett[_2_]
Macro deleting rows based in main sheet on values in another sheet
 
On Monday, June 11, 2012 3:27:10 AM UTC-5, 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

If you are saying you want delete all rows in delete where the value in col c matches cell cx then it seems that a simple datafilterautofilterfilter col c for the valuedelete. Record a macro while doing and then clean it up. If that doesn't work send file to dguillett1@gmail with examples.

pascal baro
Macro deleting rows based in main sheet on values in another sheet
 
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 Guillett[_2_]
Macro deleting rows based in main sheet on values in another sheet
 
On Monday, June 11, 2012 8:15:11 AM UTC-5, pascal baro wrote:
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?

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


All times are GMT +1. The time now is 07:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com