Macro auto insert/delete
 

Afternoon,
I am trying to use a macro that will delete any row that has a cell with the
value of "#VALUE!" in it.

I have tried several macro examples found on this and other websites, one
such one like this:
-----------
Sub DeleteRows()
Dim strToDelete As String
Dim rngSrc As Range
Dim NumRows As Integer
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim ThisCol As Integer
Dim J As Integer
Dim DeletedRows As Integer

strToDelete = "#VALUE!"
Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
ThatRow = ThisRow + NumRows - 1
ThisCol = rngSrc.Column

For J = ThatRow To ThisRow Step -1
If Cells(J, ThisCol) = strToDelete Then
Rows(J).Select
Selection.Delete Shift:=xlUp
DeletedRows = DeletedRows + 1
End If
Next J
MsgBox "Number of deleted rows: " & DeletedRows
End Sub
----------------
Except the line containing "strToDelete = "#VALUE!"" throws an error and the
program will not continue. It works fine if I use "strToDelete = "bob"".
I looked up an escape character, it seems to be " so I tried ""#VALUE"!"
and many other variants, no success.
Any suggestions on how to delete rows that contain #VALUE! ?


Thank you for taking your time to read and respond.

Macro auto insert/delete
 

Sorry, Excel 2003.

Macro auto insert/delete
 

I would like to apologize, after I posted I figured out the answer:
Delete all rows with the specified cell that is not numeric!

Have a great day.

Macro auto insert/delete
 

or...


If Cells(J, ThisCol).Text = strToDelete Then

Eric wrote:

Afternoon,
I am trying to use a macro that will delete any row that has a cell with the
value of "#VALUE!" in it.

I have tried several macro examples found on this and other websites, one
such one like this:
-----------
Sub DeleteRows()
Dim strToDelete As String
Dim rngSrc As Range
Dim NumRows As Integer
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim ThisCol As Integer
Dim J As Integer
Dim DeletedRows As Integer

strToDelete = "#VALUE!"
Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
ThatRow = ThisRow + NumRows - 1
ThisCol = rngSrc.Column

For J = ThatRow To ThisRow Step -1
If Cells(J, ThisCol) = strToDelete Then
Rows(J).Select
Selection.Delete Shift:=xlUp
DeletedRows = DeletedRows + 1
End If
Next J
MsgBox "Number of deleted rows: " & DeletedRows
End Sub
----------------
Except the line containing "strToDelete = "#VALUE!"" throws an error and the
program will not continue. It works fine if I use "strToDelete = "bob"".
I looked up an escape character, it seems to be " so I tried ""#VALUE"!"
and many other variants, no success.
Any suggestions on how to delete rows that contain #VALUE! ?

Thank you for taking your time to read and respond.

--

Dave Peterson