CODEWITHSUNDEEP
excelvba
Maen
Maen

Reputation: 1030

How to Delete Rows in Excel Worksheet based on a Criteria

I have an excel workbook, in worksheet1 in Column A, IF the value of that column = ERR I want it to be deleted (the entire row), how is that possible?

PS: keep in mind that I have never used VBA or Macros before, so detailed description is much appreciated.

Upvotes: 1

Views: 11677

Answers (4)

Reafidy
Reafidy

Reputation: 8481

Fastest method:

Sub DeleteUsingAutoFilter()

    Application.ScreenUpdating = False

    With ActiveSheet
        .AutoFilterMode = False

        .Columns("A").AutoFilter Field:=1, Criteria1:="ERR"

        .AutoFilter.Range.Offset(1, 0).EntireRow.Delete        

        .AutoFilterMode = False
    End With

    Application.ScreenUpdating = True

End Sub

Second fastest method (lots of variations to this one too):

Sub DeleteWithFind()
    Dim rFound As Range, rDelete As Range
    Dim sAddress As String

    Application.ScreenUpdating = False

    With Columns("A")
        Set rFound = .Find(What:="ERR", After:=.Resize(1, 1), SearchOrder:=xlByRows)

        If Not rFound Is Nothing Then
            Set rDelete = rFound                
            Do
                Set rDelete = Union(rDelete, rFound)
                Set rFound = .FindNext(rFound)
            Loop While rFound.Row > rDelete.Row                
        End If

        If Not rDelete Is Nothing Then rDelete.EntireRow.Delete

    End With

    Application.ScreenUpdating = True

End Sub

Autofilter method for multiple sheets:

Sub DeleteUsingAutoFilter()
    Dim vSheets As Variant
    Dim wsLoop As Worksheet

    Application.ScreenUpdating = False

    '// Define worksheet names here
    vSheets = Array("Sheet1", "Sheet2")

    For Each wsLoop In Sheets(vSheets)

         With wsLoop
             .AutoFilterMode = False

             .Columns("A").AutoFilter Field:=1, Criteria1:="ERR"

             .AutoFilter.Range.Offset(1, 0).EntireRow.Delete

             .AutoFilterMode = False
         End With

    Next wsLoop

    Application.ScreenUpdating = True

End Sub

Upvotes: 2

niko
niko

Reputation: 9393

  sub delete_err_rows()
      Dim Wbk as Excel.workbook  'create excel workbook object
      Dim Wsh as worksheet       ' create excel worksheet object 
      Dim Last_row as long
      Dim i as long
      Set Wbk = Thisworkbook ' im using thisworkbook, assuming current workbook
                             ' if you want any other workbook just give the name 
                             ' in invited comma as "workbook_name"
      Set Wsh ="sheetname"   ' give the sheet name here 
      Wbk.Wsh.activate
     ' it means Thisworkbook.sheets("sheetname").activate
     ' here the sheetname of thisworkbook is activated
     ' or if you want looping between sheets use thisworkbook.sheets(i).activate
     ' put it in loop , to loop through the worksheets
     ' use thisworkbook.worksheets.count to find number of sheets in workbook
     Last_row = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row 'to find the lastrow of the activated sheet
          For i = lastrow To 1 step -1
             if activesheet.cells(i,"A").value = "yourDesiredvalue"
                  activesheet.cells(i,"A").select  ' select the row
                  selection.entirerow.delete       ' now delete the entire row
             end if
          Next i
   end sub

Note any operations that you do using activesheet , will be affected on the currently activated sheet

As your saying your a begginner, why dont you record a macro and check out, Thats the greatest way to automate your process by seeing the background code

Just find the macros tab on the sheet and click record new macro , then select any one of the row and do what you wanted to do , say deleting the entire row, just delete the entire row and now go back to macros tab and click stop recording .

Now click alt+F11 , this would take you to the VBA editor there you find some worksheets and modules in the vba project explorer field , if you dont find it search it using the view tab of the VBA editor, Now click on module1 and see the recorded macro , you will find something like these

        selection.entirerow.delete

I hope i helped you a bit , and if you need any more help please let me know, Thanks

Upvotes: 2

brettdj
brettdj

Reputation: 55702

Using an autofilter either manually or with VBA (as below) is a very efficient way to remove rows

The code below

  1. Works on the entire usedrange, ie will handle blanks

  2. Can be readily adpated to other sheets by changing strSheets = Array(1, 4). ie this code currently runs on the first and fourth sheets

     Option Explicit


Sub KillErr()
Dim ws As Worksheet
Dim lRow As Long
Dim lngCol As Long
Dim rng1 As Range
Dim strSheets()
Dim strws As Variant
strSheets = Array(1, 4)
For Each strws In strSheets
    Set ws = Sheets(strws)
    lRow = ws.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lngCol = ws.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    Application.ScreenUpdating = False
    ws.Rows(1).Insert
    Set rng1 = ws.Range(ws.Cells(1, lngCol), ws.Cells(lRow + 1, lngCol))
    With rng1.Offset(0, 1)
        .FormulaR1C1 = "=RC1=""ERR"""
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
        On Error Resume Next
        .EntireColumn.Delete
        On Error GoTo 0
    End With
Next
Application.ScreenUpdating = True
End Sub

Upvotes: 2

JDunkerley
JDunkerley

Reputation: 12505

Assuming there are always values in the cells in column A and that the data is in the first sheet, then something like this should do what you want:

Sub deleteErrRows()
    Dim rowIdx As Integer
    rowIdx = 1

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(1)

    While ws.Cells(rowIdx, 1).Value <> ""
        If ws.Cells(rowIdx, 1).Value = "ERR" Then
            ws.Cells(rowIdx, 1).EntireRow.Delete
        Else
            rowIdx = rowIdx + 1
        End If
    Wend
End Sub

Upvotes: 0

Related Questions