Deleting rows when cell has "#DIV/0!" error
Hi,
I'm currently using this code to delete rows with balnk cells. However, i have changes the formatting of my sheet and the cells are no longer blank they have the "#DIV/0!" error. How can I delete the entire row when the cell in column D has that value? Sub DeleteRowsIfDIsBlank() With payrollsht Dim rg As Range, rgBlank As Range Set rg = Cells.Range("D:D") On Error Resume Next Set rgBlank = rg.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rgBlank Is Nothing Then Else rgBlank.EntireRow.Delete End If End With End Sub |
Deleting rows when cell has "#DIV/0!" error
Hi matt,
In Excel 2003 I creates this: Sub DeleteRowsIfDIsError() Dim i As Integer With ActiveSheet ' start with last used row For i = Cells(65536, 4).End(xlUp).Row _ To 1 Step -1 If IsError(Cells(i, 4).Value) Then Rows(i).EntireRow.Delete End If Next End With End Sub HTH, Wouter |
Deleting rows when cell has "#DIV/0!" error
Hi Mattg,
If #DIV/0! is the only error in Column D you could try a loop with Set rgError = rg.SpecialCells(xlCellTypeFormulas, xlErrors) or something like that (bearing in mind the limitations of Specialcells) HTH "mattg" wrote in message ... Hi, I'm currently using this code to delete rows with balnk cells. However, i have changes the formatting of my sheet and the cells are no longer blank they have the "#DIV/0!" error. How can I delete the entire row when the cell in column D has that value? Sub DeleteRowsIfDIsBlank() With payrollsht Dim rg As Range, rgBlank As Range Set rg = Cells.Range("D:D") On Error Resume Next Set rgBlank = rg.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rgBlank Is Nothing Then Else rgBlank.EntireRow.Delete End If End With End Sub |
All times are GMT +1. The time now is 04:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com