VBA to delete rows based on empty cell

[Johnson Boni]

Hi.

Brand new here and to the world of VBA and MACROS.

I need help with a certain situation I have.

I have a workbook with twelve worksheets, sheet 1 - sheet 12. A lot of data but what I need to concentrate on is column d. I would like assistance with a vba code that would delete the entire row if the cell in column d is empty. There are three groupings. I really have no control over this as the template is controlled and not much I can do to change it.

Lets say the ranges are D3:D28, D35:D40, and D50:D55. Should any of the cells be empty the entire row to be deleted and to repeat for all twelve sheets.

Thank you!

 

[Michael M]

Are the cells completely empty....or are they empty as a result of a formula??
What is in between each set of data in Column "D"

 

[Johnson Boni]

Thanks for taking the time.

There is a function involved that does a evaluation based on other cells value. If true it populates the cell with a value if false leaves the cell blank.

 

[Johnson Boni]

Sorry for double posting but between each group of data is a header (s) that describe the data set that follows it.

To keep it simple a good example would be

2019 total sales and loss
data set
2018 sales summary and loss
data set
2017 sales summary and loss
data set

The first group is current and more detailed hence a larger group associated with it.

 

[Peter_SSs]

Welcome to the MrExcel board!

Lets say the ranges are D3:D28, D35:D40,

Just checking for the above example.

1. D29:D34 are blank but you don't want those rows deleted?
   
   
2. Do D29:D35 cells also contain formulas returning "" or are they completely empty?
   
   
3. In D3:D28 and again in D35:D40 there may be some blank cells ("") and you want those rows deleted?
   
   
4. For the formulas in column D that sometimes return "", what do they return if not ""? Is it text, numbers, combination?
   
   
5. If I am right with 1 & 3, is there another column in rows 3:28 and rows 35:40 etc that contains no blank cells at all?

 

[petertenthije]

Would you be willing to rewrite your formula in column D, so that you do not get an empty cell but an error message? If so, then the below macro will work to select the cells with errors and remove the applicable rows.

Sub DeleteBlankRows3()
On Error Resume Next
Range("D50:D55").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("D35:D40").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("D3:D28").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

To rewrite the formule, use the NA() formula. For example:
=if(A1+B1=C1,C1,NA())



edited to add:
If you are going to use this suggestion, then make sure to FIRST remove the contents of cells D50:D55, followed by D35:40 and then D3:D28. If you first delete the contents of D3:D28, then the contents of D35:D40 and D50:D55 will shift down, this would affect the macro.

 

[footoo]

edited to add:
If you are going to use this suggestion, then make sure to FIRST remove the contents of cells D50:D55, followed by D35:40 and then D3:D28. If you first delete the contents of D3:D28, then the contents of D35:D40 and D50:D55 will shift down, this would affect the macro.

Alternatively, just change the code to :

Sub DeleteBlankRows3()
On Error Resume Next
[D:D].SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

 

[Johnson Boni]

Thank you all for your responses. I think the best thing i can do is attach an example of what i am working with. This is just dummy data means nothing but it is an exact of what I am working with. The primary key is the Wafer No column, lets call that column D. You can see the value returned when no data is found, "N/A". So when this happens i want the whole row to delete. At the moment this is done manually through a lot of pages, usually twelve. All your responses are much appreciated. Thank you.

 

[petertenthije]

This willl remove rows with error messages in cells D50:D55, D35:D40 and D3:D28 for 12 sheets named Sheet1 through Sheet12.

Sub DeleteBlankRows1()
Sheets("Sheet1").Activate
Call DeleteBlankRows2()
Sheets("Sheet2").Activate
Call DeleteBlankRows2()
Sheets("Sheet3").Activate
Call DeleteBlankRows2()
Sheets("Sheet4").Activate
Call DeleteBlankRows2()
Sheets("Sheet5”).Activate
Call DeleteBlankRows2()
Sheets("Sheet6").Activate
Call DeleteBlankRows2()
Sheets("Sheet7").Activate
Call DeleteBlankRows2()
Sheets("Sheet8").Activate
Call DeleteBlankRows2()
Sheets("Sheet9").Activate
Call DeleteBlankRows2()
Sheets("Sheet10").Activate
Call DeleteBlankRows2()
Sheets("Sheet11").Activate
Call DeleteBlankRows2()
Sheets("Sheet12").Activate
Call DeleteBlankRows2()
End sub

Sub DeleteBlankRows2()
On Error Resume Next
Range("D50:D55").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("D35:D40").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("D3:D28").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

 

[Johnson Boni]

This willl remove rows with error messages in cells D50:D55, D35:D40 and D3:D28 for 12 sheets named Sheet1 through Sheet12.

Sub DeleteBlankRows1()
Sheets("Sheet1").Activate
Call DeleteBlankRows2()
Sheets("Sheet2").Activate
Call DeleteBlankRows2()
Sheets("Sheet3").Activate
Call DeleteBlankRows2()
Sheets("Sheet4").Activate
Call DeleteBlankRows2()
Sheets("Sheet5”).Activate
Call DeleteBlankRows2()
Sheets("Sheet6").Activate
Call DeleteBlankRows2()
Sheets("Sheet7").Activate
Call DeleteBlankRows2()
Sheets("Sheet8").Activate
Call DeleteBlankRows2()
Sheets("Sheet9").Activate
Call DeleteBlankRows2()
Sheets("Sheet10").Activate
Call DeleteBlankRows2()
Sheets("Sheet11").Activate
Call DeleteBlankRows2()
Sheets("Sheet12").Activate
Call DeleteBlankRows2()
End sub

Sub DeleteBlankRows2()
On Error Resume Next
Range("D50:D55").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("D35:D40").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("D3:D28").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

Thank you I will implement this tomorrow and will let you know how things work out. My day is over.