Delete row if cell has nothing

[ina]

Hello,

I woulk like to delete row when in your cell the value is N/A#

This is my code

I have a bi-dimensional var (1 to 1000, 1 to 9) as variant


For n = LBound(var) To UBound(var)

Do
Set rng = Range("A:A").Find(What:="N/A#", _
After:=Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
rng.EntireRow.Delete

Loop While Not (rng Is Nothing)

Next n

this is the row

MARIANNE | DATE| N/A# | 125.-

I would like to delete this row do you think you need to use cell

ina

 

[Bob Phillips]

Sub DeleteNAs()
Dim oCell As Range
Dim rng As Range
Dim rngDelete As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16)
On Error GoTo 0

If Not rng Is Nothing Then
For Each oCell In rng
If IsError(oCell.Value) Then
If oCell.Value = CVErr(xlErrNA) Then
If rngDelete Is Nothing Then
Set rngDelete = oCell
Else
Set rngDelete = Union(oCell, rngDelete)
End If
End If
End If
Next oCell
End If

If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[macropod]

Hi ina,

Assuming UBound(Var) To LBound(Var) is meant to define the rows to search,
try:

Sub CleanUp()
Dim R As Long
Dim C As Integer
With ActiveSheet
For R = UBound(Var) To LBound(Var) Step -1
For C = 1 To .UsedRange.Columns.Count
If .Cells(R, C).Text = "#N/A" Then
.Rows(R).EntireRow.Delete
Exit For
End If
Next C
Next R
End With
End Sub

Cheers

 

[ina]

thank you a lot for this two answers
Ina