Help with adding on to this macro?

[bdunk]

Hi, I have this macro (code below) that asks for input from a user to delete rows that have a certain value or less in the 'C' column. This works perfectly however how can I adjust this macro to to leave the cells that have no value (blank) alone. THis is a header column that I would like to leave and is currently being removed by the current code.

Thanks for the help.

bDunk


Sub Delete_Part_Input()
Dim Qty As Integer, i As Integer, ans As Variant

Qty = Application.InputBox("What total quantities would you like to delete? Note: This is the value located in the C column.", Type:=1)
If Qty = 0 Then Exit Sub

ans = MsgBox("All totals with a quantity of " & Qty & _
" or less will be deleted." & vbCrLf & vbCrLf & _
"Do you want to continue?", vbYesNo + vbExclamation)

If ans = vbNo Then Exit Sub

For i = Qty To 0 Step -1
Columns(3).Replace what:=i, replacement:="", LookAt:=xlWhole
Next i
Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Andrew Poulsom]

If it's row 1 you want to keep, try:

Columns(3).Resize(Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

instead of:

Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[bdunk]

Andrew, Thanks for the reply. It is not always row 1. It is any row that has nothing for a value in the C column for that row. The macro will remove any rows that have whatever is input in the C column including no values. I want the macro to remove the rows with values less than what the user inputs in columns C but to leave the rows alone that no value at all in the C column. Hope this helps to further explain what I am looking for.

bdunk

 

[Andrew Poulsom]

This temporarily replaces the existing blanks with "Leave", so they don't get deleted:

Sub Delete_Part_Input()
    Dim Qty As Integer, i As Integer, ans As Variant

    Qty = Application.InputBox("What total quantities would you like to delete? Note: This is the value located in the C column.", Type:=1)
    If Qty = 0 Then Exit Sub

    ans = MsgBox("All totals with a quantity of " & Qty & _
    " or less will be deleted." & vbCrLf & vbCrLf & _
    "Do you want to continue?", vbYesNo + vbExclamation)

    If ans = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    Columns(3).SpecialCells(xlCellTypeBlanks).Value = "Leave"

    For i = Qty To 0 Step -1
        Columns(3).Replace what:=i, replacement:="", LookAt:=xlWhole
    Next i
    Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns(3).Replace what:="Leave", replacement:="", LookAt:=xlWhole
    Application.ScreenUpdating = True
End Sub

 

[bdunk]

Wow that is super. Thanks so much for your help Andrew.

Bdunk