Help with adding on to this macro?

bdunk

Active Member
Joined
Aug 1, 2002
Messages
290
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Aug 1, 2002
Messages
290
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This temporarily replaces the existing blanks with "Leave", so they don't get deleted:

Code:
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

Active Member
Joined
Aug 1, 2002
Messages
290
Wow that is super. Thanks so much for your help Andrew.

Bdunk
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,439
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top