• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

delete row if excel 2007 vba

Hello all

I need to be able to delete rows in sheet (" Fleet") if the name in column D starts with one of the names in sheet("3P carrier list") column A.

This would be done by clicking on the delete row if button.

How can i do this please?
Thanks again for your help.


Classeur1.xlsm
0
Wilder1626
Asked:
Wilder1626
1 Solution
 
SiddharthRoutCommented:
Is this what you want?

Please paste this in a module.

Sub DeleteRows()
    Dim i As Long, j As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long
    Dim Rng As Range
    
    Set ws1 = Sheets("Fleet")
    Set ws2 = Sheets("3P carrier list")
    
    ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To ws2LastRow
        For j = ws1LastRow To 4 Step -1
            If InStr(1, ws1.Range("D" & j).Value, ws2.Range("A" & i).Value, vbTextCompare) Then
                If Rng Is Nothing Then
                    Set Rng = ws1.Rows(j)
                Else
                    Set Rng = Union(Rng, ws1.Rows(j))
                End If
            End If
        Next j
    Next i
    
    Rng.Delete shift:=xlUp
End Sub

Open in new window


Sid
0
 
Wilder1626Author Commented:
This is perfect.

Thanks again for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now