Need VBA coding

[MohanBabu1023]

Need VBA to delete the entire row, the cell that contains the value order number : XXXX in A column. note:- order number in the entire A column
sample file attached below

 

[ThauThème]

Hi MohanBabu,

If you want to delete all the rows containing "Order Number" in column A, try this way :

Sub Macro1()
Dim S As Worksheet
Dim LR As Integer
Dim CT As Variant
Dim I As Integer
Dim RA As Range

Set S = Sheets("Sheet1")
LR = S.Cells(Application.Rows.Count, 1).End(xlUp).Row
CT = S.Range("A1:A" & LR)
Set RA = S.Range("B1")
For I = 1 To UBound(CT, 1)
    If InStr(1, CT(I, 1), "Order Number") <> 0 Then
        Set RA = IIf(RA.Address = "$B$1", S.Rows(I), Application.Union(RA, S.Rows(I)))
    End If
Next I
If RA.Address <> "$B$1" Then RA.Delete
End Sub

 

[Marc L]

Hi,

easy with a filter :​

Sub Demo()
                    Application.ScreenUpdating = False
    With Sheet1
        With Range(.Cells(1), .Cells(.Rows.Count, 1).End(xlUp))
            .AutoFilter 1, "Order Number  : *"
            .Offset(1).EntireRow.Delete xlShiftUp
            .AutoFilter
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[MohanBabu1023]

Thank u G,
Its working, awesome.

B. Mohan.

 

[MohanBabu1023]

Marc L,
i also tried with filter, because of blank in between the rows i got struck at that point.

 

[Marc L]

My code works like a breeze on my side with differents Excel versions
with your attached file ! And the last line detection is the same than Robert …
​