Help with deleting rows

  • Thread starter Thread starter Kari
  • Start date Start date
K

Kari

Hello Gurus!
The problems is this:
If there is value in cell A and B goto next row
If there is value in cell A and B is empty delete row
If cell A is empty and there is value in cell B delete row
If there is no value in cell A and no value in cell B stop macro

and this:
If there is value in cell A and B goto next row
If there is value in cell A and B is empty delete row
If cell A is empty and there is value in cell B next row
If there is no value in cell A and no value in cell B stop macro

t. Kari
 
Try

Sub deleterowsifbothblank()
lr = Cells.Find(What:="*", _
LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox lr
For i = lr To 1 Step -1
If Len(Trim(Cells(i, 1))) < 1 _
Or Len(Trim(Cells(i, 2))) < 1 Then Rows(i).Delete
Next i
End Sub
 
Kari,

First one

On Error Resume Next
Range("A:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Second one

On Error Resume Next
Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


HTH,
Bernie
MS Excel MVP
 
This looks at all rows starting at 1

Sub deletIt1()
myflag = False
J = 1
Do While myflag = False
If Cells(J, 1) <> "" And Cells(J, 2) <> "" Then
'do nothing
J = J + 1
ElseIf Cells(J, 1) = "" And Cells(J, 2) = "" Then
myflag = True
Else
Cells(J, 1).EntireRow.Delete
End If

Loop
End Sub
 
Back
Top