do..loop

[Guest]

I would like to compare each cell in column B (which is month) with cell A1
(showing this month). If the text in cell A1 is same as column B, then keep
the row. Otherwise, delete the row. I don't what's wrong with below coding!!
Anyone can help me or any other good sugguestion?
Thank you for your help.

Range("B6").Select

Do

If ACTIVCECELL.Text = Range("A1").Text Then
ActiveCell.Offset(1, 0).Select
Else
Selection.Delete Shift:=xlUp
End If
Loop Until ActiveCell.Text = "END"

 

[Guest]

try:

If ACTIVeCELL= Range("A1") Then

 

[Bob Phillips]

For i = Cells(Rows.Count, "B").End(xlUp).Row To 6 Step -1
If Cells(i, "B").Text <> Range("A1").Text Then
Cells(i, "B").Delete Shift:=xlUp
End If
Next i


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

The coding seems delete cells (in column b) but not the entire row!! Would
you please tell me what should I do?
Thanks

 

[Dave Peterson]

And to delete the entire row:

For i = Cells(Rows.Count, "B").End(xlUp).Row To 6 Step -1
If Cells(i, "B").Text <> Range("A1").Text Then
rows(i).Delete
End If
Next i

 

[Bob Phillips]

judging from her code, she didn't want to do that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Yep. But on the follow up to Toppers, she asked how to change his code to
delete the entire row.

So I jumped in <bg>.