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;">

1down votefavorite ​

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.

<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;">
</tbody>

1down votefavorite ​

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.

<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;">
</tbody>

</body>

down votefavorite ​

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

<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;">
</tbody>

 

[ahsan4pak]

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