Delete row if cell has nothing
 

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

Delete row if cell has nothing
 

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

On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, 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)

"ina" wrote in message
oups.com...
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

Delete row if cell has nothing
 

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" wrote in message
oups.com...
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

Delete row if cell has nothing
 

thank you a lot for this two answers
:) Ina