help with macro

[Bobbo]

Need help with a simple excel macro
What I need it to do is :
if a cell in column B=has the work "No" in it and the cell in column C that
is next to it is blank or empty the row needs to be deleted

Thanks
Bob

 

[Patrick Molloy]

copy this code to a standard module:=

Sub demo()
dim cell as range
for each cell in Range("B:B").Cells
if cell.value = "No" AND cell.Offset(,1)="" then
Rows(cell.Row).Delete
end if
next
End Sub

OPen the development environment, ALT+F11
Add a module - from the INSERT menu select MODULE

 

[Dave Peterson]

That could cause trouble.

It's usually better to delete rows starting from the bottom up.

Option Explicit
Sub Testme()
dim LastRow as long
dim FirstRow as long
dim iRow as long

with worksheets("sheet99999") 'activesheet???
firstrow = 2 'headers in row 1????
lastrow = .cells(.rows.count,"A").end(xlup).row
for irow = lastrow to firstrow step -1
if lcase(.cells(irow,"B").value) = lcase("no") then
if .cells(irow,"C").value = "" then
.rows(irow).delete
end if
end if
next irow
end with
end sub

======
But if the OP wants to work from the top toward the bottom:

Option Explicit
Sub Testme2()
Dim myCell As Range
Dim myRng As Range
Dim DelRng As Range

with worksheets("sheet99999") 'activesheet???
Set myRng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase("no") Then
If myCell.Offset(0, 1).Value = "" Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(DelRng, myCell)
End If
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
DelRng.EntireRow.Delete
End If

End Sub

 

[Bobbo]

Thanks,
It works but I have to run it more than once to get all rows removed.
Any suggestions

 

[Rick Rothstein]

Or, with this slightly shorter routine, as you come to them...

Sub DeleteNoBlankCombo()
On Error GoTo NoMore
Do
ActiveSheet.Range("B:B").Find(What:="No", LookAt:=xlWhole, _
LookIn:=xlValues, MatchCase:="False").EntireRow.Delete
Loop
NoMore:
End Sub

Where the OP can specify an specific worksheet reference in place of the
ActiveSheet one that I used.

 

[Patrick Molloy]

for rw = range("B65000").End(xlUp).Row to 1 step -1
if cells(rw,"B")="No" then
if cells(rw,"C")="" then
rows(rw).delete
End if
end if
next

 

[Dave Peterson]

I think you missed that portion about column C being empty at the same time.

 

[Rick Rothstein]

<gulp> I sure did!

--
Rick (MVP - Excel)

I think you missed that portion about column C being empty at the same
time.

 

[Dave Peterson]

Never happens to me -- Hey! Stop laughing!!!!

<gulp> I sure did!