swjtx99
asked on
Delete rows based on a column on another sheet
Hi,
I need VBA solution to delete any row on Sheet1 where there is no match between column D on Sheet1 and column A on Sheet2.
Data will always be a text string and the number of rows on either sheet will vary.
Example attached.
Thanks in advance,
swjtx99
Example.xlsx
I need VBA solution to delete any row on Sheet1 where there is no match between column D on Sheet1 and column A on Sheet2.
Data will always be a text string and the number of rows on either sheet will vary.
Example attached.
Thanks in advance,
swjtx99
Example.xlsx
ASKER
Hi,
Thanks for the reply. This appears to work but I haven't been able to confirm because my sheet is 45000 rows and it's been over 20 minutes and it's still going. Is there a more expedient method?
Thanks,
Thanks for the reply. This appears to work but I haven't been able to confirm because my sheet is 45000 rows and it's been over 20 minutes and it's still going. Is there a more expedient method?
Thanks,
You could try disabling events, turning off screen updating and setting calculations to manual while the code runs.
Is the data in the attached workbook a 'real' representation of your data?
I ask because there could be other methods to speed things, eg using arrays, but they would be dependent on the actual data.
Option Explicit
Sub DeleteRows()
Dim rng As Range
Dim rngNames As Range
Dim Res As Variant
Dim I As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
With Sheets("Sheet2")
Set rngNames = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
For I = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
Set rng = Sheets("Sheet1").Range("D" & I)
Res = Application.Match(rng.Value, rngNames, 0)
If IsError(Res) Then
rng.EntireRow.Delete xlShiftUp
End If
Next I
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Is the data in the attached workbook a 'real' representation of your data?
I ask because there could be other methods to speed things, eg using arrays, but they would be dependent on the actual data.
ASKER
Hi,
Yes it is an accurate representation of the format. I have a few more actual "owners" and as stated my actual sheet is 45000 rows and more columns but it's doing the same thing which is comparing owners and if they don't exist, I need to delete the row. I just cut it down to 500 and inserted:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
and it still took about 30 seconds to run. I have an i7 8GB and nothing else running. Would an array method be faster?
Thanks for your help,
swjtx99
but it still took
Yes it is an accurate representation of the format. I have a few more actual "owners" and as stated my actual sheet is 45000 rows and more columns but it's doing the same thing which is comparing owners and if they don't exist, I need to delete the row. I just cut it down to 500 and inserted:
Application.ScreenUpdating
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
and it still took about 30 seconds to run. I have an i7 8GB and nothing else running. Would an array method be faster?
Thanks for your help,
swjtx99
but it still took
ASKER
Hi,
Might help if I mention there are 17 columns, A-Q.
Thanks,
swjtx99
Might help if I mention there are 17 columns, A-Q.
Thanks,
swjtx99
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi imnorie,
Thanks, Excellent and very fast.
Regards,
swjtx99
Thanks, Excellent and very fast.
Regards,
swjtx99
Open in new window