Need Help Deleting Rows Based on Column Values

[RJD1993]

Sub DeleteRowWithContents()


Sheets("PS4 Base Sheet").Select
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
'========================================================================
Last = Cells(Rows.Count, "C").End(xlUp).Row
For i = Last To 1 Step -1

If (Cells(i, "C").Value) = "2016" Then
End If
If (Cells(i, "C").Value) = "TBA" Then
End If
If (Cells(i, "C").Value) = "N/A" Then
End If
If (Cells(i, "C").Value) = "Q4 2015" Then
End If
If (Cells(i, "C").Value) = "Q1 2016" Then
End If
If (Cells(i, "C").Value) = "Q2 2016" Then

'Cells(i, "C").EntireRow.ClearContents 'USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW

Cells(i, "C").EntireRow.Delete

End If

Next i

End Sub


Here's some code I'm working on, I'm new to VBA. I'm trying to delete a row if the values above are in column C. I found some code that I based it on and I'm not getting any errors, but it isn't actually accomplishing anything when I run it. I don't really have any idea what I'm doing.

[mancubus]

welcome to the forum.

use code tags when posting your code. clicking # button inserts code tags.

try below macros with a copy of your file.

i assume topleft cell of your table is A1 and Row 1 contains column headers.




Sub vbax_53939_DeleteRowsBasedOnCellValue_If()

Dim i As Long

With Worksheets("PS4 Base Sheet")
For i = .Range("C" & .Rows.Count).End(xlUp).Row To 2 Step -1
If .Cells(i, 3).Value = 2016 Or .Cells(i, 3).Value = "TBA" Or .Cells(i, 3).Value = "N/A" _
Or .Cells(i, 3).Value = "Q4 2015" Or .Cells(i, 3).Value = "Q1 2016" Or .Cells(i, 3).Value = "Q2 2016" Then
.Rows(i).Delete
End If
Next i
End With

End Sub


or, as an alternative (better)


Sub vbax_53939_DeleteRowsBasedOnCellValue_SelectCase()

Dim i As Long

With Worksheets("PS4 Base Sheet")
For i = .Range("C" & .Rows.Count).End(xlUp).Row To 2 Step -1
Select Case .Cells(i, 3).Value
Case 2016, "TBA", "N/A", "Q4 2015", "Q1 2016", "Q2 2016"
.Rows(i).Delete
Case Else
'do nothing
End Select
Next i
End With

End Sub

[mancubus]

and, imo, this is far better



Sub vbax_53939_DeleteRowsBasedOnCellValue_AutoFilter()

Dim i As Long
Dim CritArray

CritArray = Array("2016", "TBA", "N/A", "Q4 2015", "Q1 2016", "Q2 2016")

With Worksheets("PS4 Base Sheet")
.AutoFilterMode = False
For i = LBound(CritArray) To UBound(CritArray)
.Cells(1).AutoFilter Field:=3, Criteria1:=CritArray(i)
With .AutoFilter.Range
If .Rows.Count > 1 Then
.Columns(1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
End With
Next i
.AutoFilterMode = False
End With

End Sub