How to create a Macro to delete rows based on a certain value in a cell

tctran

New Member
Joined
Sep 12, 2014
Messages
7
Hello everyone,

I have an Excel Spreadsheet with a column that contain a value of either 1 or 0 and I would like to create a Macro to delete the rows that contain a 0 value on a particular column. For example, if cell D5 has a value of 0 I'd like the Macro to delete the entire row 5 and move the undeleted data up a row. I know I can sort the spreadsheet and manually delete rows that contain 0 but would like to build a Macro for the end user to click the button to automatically delete the unecessary rows. I've never created a Macro before and would really appreciate your help in guiding me to create the Macro.

Thank you so much!!!
TC
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Code:
Sub deleteRows()
    For x = Cells(Rows.Count, "D").End(xlUp).Row To 1 Step -1
        If Cells(x, 4) = 0 Then Cells(x, 1).EntireRow.Delete
    Next x
End Sub
 
Upvote 0
Hello NeonRedSharpie,

Thank you so much for the prompt response! The code you've provided worked perfectly! I'm also wondering if this can be done... Instead of deleting the rows can the Macro be built to copy these rows and either paste the data to a different sheet (New Data sheet) or automatically create a new sheet and paste the info to the new sheet? Sorry for the many questions. I'm a newbie to VBA and Macro.

Thanks again!
TC
 
Upvote 0
Hello NeonRedSharpie,

Thank you so much for the prompt response! The code you've provided worked perfectly! I'm also wondering if this can be done... Instead of deleting the rows can the Macro be built to copy these rows and either paste the data to a different sheet (New Data sheet) or automatically create a new sheet and paste the info to the new sheet? Sorry for the many questions. I'm a newbie to VBA and Macro.

Thanks again!
TC

It sure can. Which do you want though? A new sheet or a sheet named "New Data Sheet"? Also, do you want previous data cleared from the "New Sheet" or just added at the end?
 
Upvote 0
It sure can. Which do you want though? A new sheet or a sheet named "New Data Sheet"? Also, do you want previous data cleared from the "New Sheet" or just added at the end?

I'd like the macro to create a new sheet within the spreadsheet and would like to add the data to the the end. It's amazing what MS Excel can do. I've got a lot to learn. :)

Thank you,
TC
 
Upvote 0
Code:
Sub deleteRows()
    For x = Cells(Rows.Count, "D").End(xlUp).Row To 1 Step -1
        If Cells(x, 4) = 0 Then Cells(x, 1).EntireRow.Delete
    Next x
End Sub

Hello NeonRedSharpie,
Is it possible to modify this macro to delete rows in other sheets where the column D in all sheets contain a "0" value? I'd like to build one macro that delete rows in multiple sheets within the workbook. Any help you can provide would be greatly appreciated!

Thank you,
TC
 
Upvote 0
Hello NeonRedSharpie,
Is it possible to modify this macro to delete rows in other sheets where the column D in all sheets contain a "0" value? I'd like to build one macro that delete rows in multiple sheets within the workbook. Any help you can provide would be greatly appreciated!

Thank you,
TC



Code:
Sub deleteRows()For y = 1 To Sheets.Count
    With Sheets(x)
        For x = .Cells(Rows.Count, "D").End(xlUp).Row To 1 Step -1
            If .Cells(x, 4) = 0 Then .Cells(x, 1).EntireRow.Delete
        Next x
    End With
Next y
End Sub
 
Upvote 0
Code:
Sub deleteRows()For y = 1 To Sheets.Count
    With Sheets(x)
        For x = .Cells(Rows.Count, "D").End(xlUp).Row To 1 Step -1
            If .Cells(x, 4) = 0 Then .Cells(x, 1).EntireRow.Delete
        Next x
    End With
Next y
End Sub


This work perfectly! Thank you very much!!!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top