Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows when cell text reads "Research" | Excel Programming | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Look for cell containing "Initial" then if the next cell after equals "Final" then delete both rows. | Excel Programming | |||
Suppress Error Dialog: "Data may exist in the sheet(s) selected for deleting...." | Excel Programming | |||
Deleting groups of continuous rows where col K = "x" | Excel Programming |