Solved

Delete rows based on a column on another sheet

Posted on 2014-12-04
7
142 Views
Last Modified: 2014-12-04
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
0
Comment
Question by:swjtx99
  • 4
  • 3
7 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40481292
Perhaps.
Option Explicit

Sub DeleteRows()
Dim rng As Range
Dim rngNames As Range
Dim Res As Variant
Dim I As Long

    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

End Sub

Open in new window

0
 

Author Comment

by:swjtx99
ID: 40481450
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,
0
 
LVL 33

Expert Comment

by:Norie
ID: 40481473
You could try disabling events, turning off screen updating and setting calculations to manual while the code runs.
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

Open in new window


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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:swjtx99
ID: 40481529
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
0
 

Author Comment

by:swjtx99
ID: 40481539
Hi,

Might help if I mention there are 17 columns, A-Q.

Thanks,

swjtx99
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40481589
Strange, I tried the code on approx 45000 rows and it seemed quite speedy.

Anyway, perhaps doing the deletion in one go might help speed things up.
Option Explicit



Sub DeleteRows()
Dim rng As Range
Dim rngNames As Range
Dim rngDel 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
            If rngDel Is Nothing Then
                Set rngDel = rng.EntireRow
            Else
                Set rngDel = Union(rngDel, rng.EntireRow)
            End If
        End If

    Next I

    If Not rngDel Is Nothing Then
        rngDel.Delete
    End If
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
End Sub

Open in new window

0
 

Author Closing Comment

by:swjtx99
ID: 40481735
Hi imnorie,

Thanks, Excellent and very fast.

Regards,

swjtx99
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question