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

[tctran]

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

 

[NeonRedSharpie]

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

 

[tctran]

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

 

[NeonRedSharpie]

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?

 

[tctran]

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

 

[tctran]

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

 

[NeonRedSharpie]

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

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

 

[tctran]

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!!!