VBA code to delete row is value in column is less than a specific defined value

[agsteel]

I am hoping to get some help write some code to help me clean up excel worksheet by deleting rows of certain criteria true for value in a specific column. I am able to get it to work when I predefine threshold in code itself but I would like to have an assumption that can be changed and it deletes based on the input or assumption cell.

Below is code I use to delete rows when value in column K is greater than 100. Rather than define 100 upfront I would like to look at value entered in another worksheet in cell C5, and only delete row if value in column K is greater than the value in "worksheet x" and cell C5.

Any help on this would be appreciated. I am not experienced in VBA and current code was found in another thread.


Sub test()
Range("A3").Select
Dim x As Long, lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 12).Value > 100 Then
Rows(x).Delete
End If
Next x

[mdmackillop]

Welcome to VBAX
This assumes you have a column header to be retained.

Sub Test()
Dim x As Single
x = Sheet2.[C5]
With ActiveSheet
With Intersect(.Columns("K:K"), .UsedRange)
.AutoFilter Field:=1, Criteria1:=">" & x
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
End With
End Sub

[mdmackillop]

A variation on your own method

Sub test2()


Dim x As Long, lastrow As Long
Dim y As Single
Dim ws As Worksheet
Set ws = ActiveSheet
y = Application.InputBox("Select cell", Type:=8)
lastrow = ws.Cells(Rows.Count, 11).End(xlUp).Row
For x = lastrow To 1 Step -1
If ws.Cells(x, 11).Value > y Then Rows(x).Delete
Next x
End Sub

[agsteel]

Thanks for the help, really appreciated. One more quick question, how would code need to be changed if column header used to filter and to be retained is in row 3 not row 1?

[mdmackillop]

Sub Test()
Dim x As Single
x = Sheet2.[C5]
With ActiveSheet
With Intersect(.Range("K3:K" & .UsedRange.Rows.Count), .UsedRange)
.AutoFilter Field:=1, Criteria1:=">" & x
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
End With
End Sub