Delete row if a specific value exist (VBA)

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
199
Hello </PRE>
I have found these example on the internet to delete row if a specific value exist. They both work fine but I need</PRE>
it to find the specific value ("John") in examples below and select that entire row + the next 7 rows below it.</PRE>
Is these a way this can be done with the examples below either one of the examples will be fine to work with.</PRE>
Thanks</PRE>
John</PRE>
Example 1</PRE>
Sub Union_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim rng As Range With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value = "ron" Then If rng Is Nothing Then Set rng = .Cells Else Set rng = Application.Union(rng, .Cells) End If End If End If End With Next Lrow End With</PRE>
If Not rng Is Nothing Then rng.EntireRow.Delete ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End WithEnd Sub</PRE>
Example 2


Sub Find_Example() Dim calcmode As Long Dim ViewMode As Long Dim myStrings As Variant Dim FoundCell As Range Dim I As Long Dim myRng As Range Dim sh As Worksheet With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Set sh = ActiveSheet Set myRng = sh.Range("A:A") myStrings = Array("John") With sh .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False With myRng For I = LBound(myStrings) To UBound(myStrings) Do Set FoundCell = myRng.Find(What:=myStrings(I), _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If Loop Next I End With End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = calcmode End WithEnd Sub</PRE>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Hope this come out in better format</PRE>

Sub Union_Example()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p></PRE>

Dim Firstrow As Long<o:p></o:p></PRE>

Dim Lastrow As Long<o:p></o:p></PRE>

Dim Lrow As Long<o:p></o:p></PRE>

Dim CalcMode As Long<o:p></o:p></PRE>

Dim ViewMode As Long<o:p></o:p></PRE>

Dim rng As Range<o:p></o:p></PRE>

<o:p> </o:p></PRE>

With Application<o:p></o:p></PRE>

CalcMode = .Calculation<o:p></o:p></PRE>

.Calculation = xlCalculationManual<o:p></o:p></PRE>

.ScreenUpdating = False<o:p></o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

With ActiveSheet<o:p></o:p></PRE>

<o:p> </o:p></PRE>

ViewMode = ActiveWindow.View<o:p></o:p></PRE>

ActiveWindow.View = xlNormalView<o:p></o:p></PRE>

<o:p> </o:p></PRE>

Firstrow = .UsedRange.Cells(1).Row<o:p></o:p></PRE>

Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row<o:p></o:p></PRE>

<o:p> </o:p></PRE>

For Lrow = Lastrow To Firstrow Step -1<o:p></o:p></PRE>

With .Cells(Lrow, "A")<o:p></o:p></PRE>

<o:p> </o:p></PRE>

If Not IsError(.Value) Then<o:p></o:p></PRE>

<o:p> </o:p></PRE>

If .Value = "ron" Then<o:p></o:p></PRE>

<o:p> </o:p></PRE>

If rng Is Nothing Then<o:p></o:p></PRE>

Set rng = .Cells<o:p></o:p></PRE>

Else<o:p></o:p></PRE>

Set rng = Application.Union(rng, .Cells)<o:p></o:p></PRE>

End If<o:p></o:p></PRE>

End If<o:p></o:p></PRE>

<o:p> </o:p></PRE>

End If<o:p></o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

Next Lrow<o:p></o:p></PRE>

<o:p> </o:p></PRE>

End With<o:p></o:p></PRE>

If Not rng Is Nothing Then rng.EntireRow.Delete<o:p></o:p></PRE>

<o:p> </o:p></PRE>

ActiveWindow.View = ViewMode<o:p></o:p></PRE>

With Application<o:p></o:p></PRE>

.ScreenUpdating = True<o:p></o:p></PRE>

.Calculation = CalcMode<o:p></o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

End Sub<o:p></o:p></PRE>
Example 2<o:p></o:p>
<o:p></o:p>

Sub Find_Example()<o:p></o:p></PRE>

Dim calcmode As Long<o:p></o:p></PRE>

Dim ViewMode As Long<o:p></o:p></PRE>

Dim myStrings As Variant<o:p></o:p></PRE>

Dim FoundCell As Range<o:p></o:p></PRE>

Dim I As Long<o:p></o:p></PRE>

Dim myRng As Range<o:p></o:p></PRE>

Dim sh As Worksheet<o:p></o:p></PRE>

<o:p> </o:p></PRE>

With Application<o:p></o:p></PRE>

calcmode = .Calculation<o:p></o:p></PRE>

.Calculation = xlCalculationManual<o:p></o:p></PRE>

.ScreenUpdating = False<o:p></o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

Set sh = ActiveSheet<o:p></o:p></PRE>

<o:p> </o:p></PRE>

Set myRng = sh.Range("A:A")<o:p></o:p></PRE>

<o:p> </o:p></PRE>

myStrings = Array("John")<o:p></o:p></PRE>

<o:p> </o:p></PRE>

<o:p> </o:p></PRE>

With sh<o:p></o:p></PRE>

<o:p> </o:p></PRE>

.Select<o:p></o:p></PRE>

<o:p> </o:p></PRE>

ViewMode = ActiveWindow.View<o:p></o:p></PRE>

ActiveWindow.View = xlNormalView<o:p></o:p></PRE>

<o:p> </o:p></PRE>

.DisplayPageBreaks = False<o:p></o:p></PRE>

<o:p> </o:p></PRE>

With myRng<o:p></o:p></PRE>

<o:p> </o:p></PRE>

For I = LBound(myStrings) To UBound(myStrings)<o:p></o:p></PRE>

Do<o:p></o:p></PRE>

Set FoundCell = myRng.Find(What:=myStrings(I), _<o:p></o:p></PRE>

After:=.Cells(.Cells.Count), _<o:p></o:p></PRE>

LookIn:=xlFormulas, _<o:p></o:p></PRE>

LookAt:=xlWhole, _<o:p></o:p></PRE>

SearchOrder:=xlByRows, _<o:p></o:p></PRE>

SearchDirection:=xlNext, _<o:p></o:p></PRE>

MatchCase:=False)<o:p></o:p></PRE>

If FoundCell Is Nothing Then<o:p></o:p></PRE>

Exit Do<o:p></o:p></PRE>

Else<o:p></o:p></PRE>

FoundCell.EntireRow.Delete<o:p></o:p></PRE>

End If<o:p></o:p></PRE>

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on">Loop</st1:place><o:p></o:p></PRE>

Next I<o:p></o:p></PRE>

<o:p> </o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

ActiveWindow.View = ViewMode<o:p></o:p></PRE>

With Application<o:p></o:p></PRE>

.ScreenUpdating = True<o:p></o:p></PRE>

.Calculation = calcmode<o:p></o:p></PRE>

End With<o:p></o:p></PRE>

<o:p> </o:p></PRE>

End Sub<o:p></o:p></PRE>​
 
Upvote 0
Try code tags.:)

Code:
Option Explicit
 
Sub Union_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim calcmode As Long
Dim ViewMode As Long
Dim rng As Range

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With ActiveSheet
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        Firstrow = .UsedRange.Cells(1).Row

        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        For Lrow = Lastrow To Firstrow Step -1

            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then

                        If rng Is Nothing Then

                            Set rng = .Cells

                        Else

                            Set rng = Application.Union(rng, .Cells)

                        End If

                    End If

                End If

            End With

        Next Lrow

    End With

    If Not rng Is Nothing Then rng.EntireRow.Delete

    ActiveWindow.View = ViewMode

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub
 
'Example 2
Sub Find_Example()
Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet
 
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    Set sh = ActiveSheet

    Set myRng = sh.Range("A:A")

    myStrings = Array("John")

    With sh
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False

        With myRng

            For I = LBound(myStrings) To UBound(myStrings)

                Do

                    Set FoundCell = myRng.Find(What:=myStrings(I), _
                                               After:=.Cells(.Cells.Count), _
                                               LookIn:=xlFormulas, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)

                    If FoundCell Is Nothing Then
                        Exit Do
                    Else
                        FoundCell.EntireRow.Delete
                    End If

                Loop

            Next I

        End With

    End With

    ActiveWindow.View = ViewMode

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top