Thread
:
Better way to delete rows wiht #N/A?
View Single Post
#
1
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Better way to delete rows wiht #N/A?
I don't know if this is any faster but try. UN tested
Sub Delete_Rows_With_Error_Russell_Adjust()
Dim DeleteValue As String
with Sheets("HotList").range("bj13:bj3000")
DeleteValue = "#N/A"
.AutoFilter Field:=1, Criteria1:=DeleteValue
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
--
Don Guillett
SalesAid Software
"SteveC" wrote in message
...
I use the following macro obtained from someone on the board to delete all
rows that contain #N/A in a particular column... problem is that it seems
really slow... wondering if there is a faster way? Thanks for taking a
look... SteveC
Sub Delete_Rows_With_Error_Russell_Adjust()
Dim DeleteValue As String
Dim rng As Range
Sheets("HotList").Select
DeleteValue = "#N/A"
With ActiveSheet
.Range("BJ13:BJ3000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett