Forum Discussion
LesKing
Apr 01, 2024Brass Contributor
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
- OliverScheurichGold Contributor
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
- LesKingBrass ContributorThanks 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
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
- LesKingBrass ContributorThank you, Hans - you've rescued me again!!
Les King