delete complete set of row if 124124 is there anywhere in the row in each set

I want to delete complete set of row  if 124124 is there anywhere in the row in each set
this highlighted colour is only for understanding purpose
Book1--2-.xlsm
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or you may try something like this...

Sub DeleteRows()
Dim lr As Long, lc As Long
Dim Rng As Range, delRng As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = ActiveSheet.UsedRange.Columns.Count

For Each Rng In Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 1).Areas
    If Application.CountIf(Rng.Resize(, lc), "124124") > 0 Then
        If delRng Is Nothing Then
            Set delRng = Rng.Resize(Rng.Cells.Count + 1)
        Else
            Set delRng = Union(delRng, Rng.Resize(Rng.Cells.Count + 1))
        End If
    End If
Next Rng
If Not delRng Is Nothing Then
    delRng.EntireRow.Delete
End If
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Try the macro in Module1.
29099487.xlsm
0
 
Avinash SinghAuthor Commented:
Thnx Martin liss Sir and Thnx Neeraj sir for ur great support and giving ur precious time to this post
0
 
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.