Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if cell has nothing
thank you a lot for this two answers
:) Ina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird: inside of a cell I have a small box or cell I can't delete. | Excel Discussion (Misc queries) | |||
How to delete cell values withour deleting cell formulae | Excel Discussion (Misc queries) | |||
How to delete cell values without affecting cell formulae | Excel Discussion (Misc queries) | |||
Delete cell on 1 page: automatically delete on another page? | Excel Worksheet Functions | |||
Delete cell contents with input to adjacent cell | Excel Discussion (Misc queries) |