CODEWITHSUNDEEP
excelexcel-2010vba
user3088476
user3088476

Reputation: 155

Find value and delete entire row

I am trying to search for an specific cell value in a column, if value found then I need to delete the entire row. Below is the code I have so far, I am getting a global error on the Range("X22:X" & lastRow).selection line.

can anyone help and tell me where I am going wrong.

Dim LResult As String

    Set sht1 = ThisWorkbook.Worksheets("Formatted")
                lastRow = Cells(Rows.Count, x).End(xlUp).Row
                    'MsgBox "Last Row: " & lastRow    'Used to check that the find LastRow worked

            Range("X22:X" & lastRow).Select
            Cells.Find(What:="1: Request").Select
            Selection.EntireRow.Delete

Upvotes: 0

Views: 7412

Answers (3)

Tommi Sisso
Tommi Sisso

Reputation: 131

Seems like a number of answers arrived while I was sketching the solution. I'll post it anyway. We simply define the range that contains our data, and utilize for loop to check each value on the first column.

Public Sub rmv()
'first define the String that we are looking for

Dim searc As String
searchTerm = "foo"

'define the sheet we operate on

Set sht1 = ThisWorkbook.Worksheets("Sheet1")

'find last row

lastRow = sht1.Cells(Rows.Count, "B").End(xlUp).Row     '
                'MsgBox "Last Row: " & lastRow    'Used to check that the find LastRow worked

' Define the range of data

Dim fRange As Range
Set fRange = sht1.Range("B2:B" & lastRow)  ' modify this range according to your own needs

' Loop through each row of the data range

Dim i As Integer
For i = 1 To fRange.Rows.Count:

    If fRange(i, 1) = searchTerm Then
        ' here we check the first cell of the range
        ' this row contains the wanted data => remove entire row
        fRange.Rows(i).EntireRow.Delete
    End If
 Next i

 End Sub

Upvotes: 0

Ralph
Ralph

Reputation: 9444

I'd like to add an alternative solution which might be a bit faster and comes directly from Microsoft themselves: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

Option Explicit

Public Sub RemoveMatchingRows()

Dim rngFound As Range
Dim sht1 As Worksheet

Set sht1 = ThisWorkbook.Worksheets("Formatted")
With sht1.Range("X:X")
    Set rngFound = .Find(What:="1: Request")
    If Not rngFound Is Nothing Then
        While Not rngFound Is Nothing
            sht1.Rows(rngFound.Row).EntireRow.Delete
            Set rngFound = .FindNext
        Wend
    End If
End With

End Sub

Upvotes: 1

bbear
bbear

Reputation: 97

Instead of using Find, try using an if loop

Solution found here: http://www.rondebruin.nl/win/s4/win001.htm

I only adapted it to fit the information given by you

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

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


    With Sheets("Formatted")

        'select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "1: Request" Then .EntireRow.Delete
                    'This will delete each row with the Value "1: Request"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Upvotes: 1

Related Questions