Forum Discussion

LesKing's avatar
LesKing
Brass Contributor
Apr 01, 2024

Deleting Rows based on a criteria

Hi all,

I have a spreadsheet with 88 rows (which will get more rows over time). The data starts at row 2 (row 1 is a header row). Column 'I' in each row shows either "Mandatory" or "Not Required". I need to delete all the rows where Column 'I' contains the words "Not Required". I can do this manually, but would like to automate the process using a macro. I've tried and failed to write a successful one. Can anyone advise please? Thank you.

Les King

  • LesKing 

    Sub Delete_not_required()
    
    Dim rng As Range
    Dim startData As Variant
    Dim resultData As Variant
    Dim i As Long
    Dim lastrow As Long
    Dim k As Long
    Dim j As Long
    
    Range("M:U").Clear
    
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2:I" & lastrow)
    
    startData = rng.Value
    ReDim resultData(1 To rng.Rows.Count, 1 To 9)
    
    k = 1
    
    For i = 1 To UBound(startData, 1)
        If startData(i, 9) <> "Not required" Then
        For j = 1 To 9
           resultData(k, j) = startData(i, j)
           
        Next j
        k = k + 1
        End If
        
    Next i
    
    Range("M2").Resize(k, 9) = resultData
    
    End Sub
    • LesKing's avatar
      LesKing
      Brass Contributor
      Thanks for your reply Oliver. I had already got a reply from Hans Vogelaar which worked perfectly, doing exactly what I needed. So I'm going with that. But thank you so much for all your effort.
      Les King
  • LesKing 

    Sub DeleteNotRequired()
        Application.ScreenUpdating = False
        Range("I:I").AutoFilter Field:=1, Criteria1:="Not Required"
        Range("I2:I1000").SpecialCells(Type:=xlCellTypeVisible).EntireRow.Delete
        Range("I:I").AutoFilter
        Application.ScreenUpdating = True
    End Sub
    • LesKing's avatar
      LesKing
      Brass Contributor
      Thank you, Hans - you've rescued me again!!
      Les King

Resources