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

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



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

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



Patrick Molloy wrote:

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

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

--

Dave Peterson

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

"Patrick Molloy" wrote:

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



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

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
NoMo
End Sub

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

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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



Patrick Molloy wrote:

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

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

--

Dave Peterson

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

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

"Patrick Molloy" wrote:

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



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

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

Rick Rothstein wrote:

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
NoMo
End Sub

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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



Patrick Molloy wrote:

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

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

--

Dave Peterson

--

Dave Peterson

<gulp I sure did!

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
I think you missed that portion about column C being empty at the same
time.

Rick Rothstein wrote:

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
NoMo
End Sub

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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



Patrick Molloy wrote:

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

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

--

Dave Peterson

--

Dave Peterson

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

Rick Rothstein wrote:

<gulp I sure did!

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
I think you missed that portion about column C being empty at the same
time.

Rick Rothstein wrote:

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
NoMo
End Sub

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

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
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



Patrick Molloy wrote:

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

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson