G
Guest
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.
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.