Delete row if all formula-based cells in a certain range are 0 or blank

[excelgeek1234]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: votecell"]

1down votefavorite
​

[/TD]
[TD="class: postcell"] I am trying to write a code which basically looks at rows 13-33 and deletes the entire row if the cells in Columns B-M are all Blank AND column A is NOT blank.
The first code which I have written below deletes the entire row only if the cell in Column B is blank but I need all the cells in B-M to be blank in order to delete the entire row.


[/TD]

</tbody>

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: votecell"]

1down votefavorite
​

[/TD]
[TD="class: postcell"] I am trying to write a code which basically looks at rows 13-33 and deletes the entire row if the cells in Columns B-M are all Blank AND column A is NOT blank.
The first code which I have written below deletes the entire row only if the cell in Column B is blank but I need all the cells in B-M to be blank in order to delete the entire row.


[/TD]

</tbody>

</body>

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: votecell"]

down votefavorite
​

[/TD]
[TD="class: postcell"] I am trying to write a code which basically looks at rows 13-33 and deletes the entire row if the cells in Columns B-M are all Blank AND column A is NOT blank. The issue that I am having is that all my cells are referencing the value from another sheet (formula-based). When I run my code below, it does not seem to recognize these formula-based cells as "0"s even though that is there value.

It only deletes the rows which have 0's but not referencing another cell.
I do not want to have to go copy and paste everything as values before running this since I want to be able to keep the formulas.

Please take a look below and advise on how I can do this.


Sub ScheduleB()
On Error GoTo errHandler


Const TOP_ROW As Long = 13
Const BOTTOM_ROW As Long = 33


Dim rowIndex As Long


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


With ThisWorkbook.Worksheets("Schedule A Template")
For rowIndex = .Cells(BOTTOM_ROW, "A").End(xlUp).Row To TOP_ROW Step -1
If Not IsEmpty(.Cells(rowIndex, "A").Value2) Then '...column A is not blank.
If Application.WorksheetFunction.CountA(.Range(.Cells(rowIndex, "B"), .Cells(rowIndex, "M"))) = 0 Then '...all cells on row rowIndex from columns B to M are blank.
.Rows(rowIndex).Delete Shift:=xlUp
End If
End If
Next
End With


Cleanup:
On Error Resume Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub


errHandler:
MsgBox Err.Description, vbExclamation + vbOKOnly, "Error"
Resume Cleanup
End Sub





[/TD]

</tbody>

 

[ahsan4pak]

You may first filter data and select zero and blank cells to delete.