Results 1 to 6 of 6

Delete row if a specific value exist (VBA)

This is a discussion on Delete row if a specific value exist (VBA) within the Excel Questions forums, part of the Question Forums category; Hello I have found these example on the internet to delete row if a specific value exist. They both work ...

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    134

    Default Delete row if a specific value exist (VBA)

    Hello
    I have found these example on the internet to delete row if a specific value exist. They both work fine but I need
    it to find the specific value ("John") in examples below and select that entire row + the next 7 rows below it.
    Is these a way this can be done with the examples below either one of the examples will be fine to work with.
    Thanks
    John
    Example 1
    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 WithEnd 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 WithEnd Sub

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Posts
    134

    Default Re: Delete row if a specific value exist (VBA)


    Hope this come out in better format

    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

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,400

    Default Re: Delete row if a specific value exist (VBA)

    Using Example 2, try changing:

    FoundCell.EntireRow.Delete

    to:

    FoundCell.Resize(8).EntireRow.Delete
    Microsoft MVP - Excel

  4. #4
    Board Regular
    Join Date
    Apr 2006
    Posts
    2,139

    Default Re: Delete row if a specific value exist (VBA)

    What about if there are less than 7 rows between two "Johns" ?

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,635

    Default Re: Delete row if a specific value exist (VBA)

    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
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Sep 2007
    Posts
    69

    Default Delete COLUMN if a specific value exist (VBA)

    Hi Guys, with reference to the VBA code posted by Norie earlier this year in March, I was wondering if this could be adapted to carry out exactly the same function BUT for COLUMNS instead of ROWS.

    Delete row if a specific value exist (VBA)

    Any help would be much appreciated.

    Many Thanks

    Mulderman

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com