VBA deleting rows based on a particular value

[csilabgirl]

Excel 2002

On Sheet1 I have a value in cell A15. On Sheet2 I have values in column B:B. The values in the B column are actually repeated for 16 rows before the value changes and then is repeated for another 16 rows.

What I want to be able to do is create a macro that will look at the value in cell A15 Sheet1 and then go to Sheet2 and delete the rows in which the value is also seen in the B column.

I have the following code which will delete rows when the A column is blank:

Dim i As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
If Cells(i, 1).Value = "" Then Cells(i, 1).EntireRow.Delete
Next i

And I tried to amend it to do what I wanted but it didnt work. I wont post my amendment since it didnt work, but I could if it was needed.

Any help would be greatly appreciated. Thank you!

 

[VoG]

Try

Dim i As Long
With Sheets("Sheet2")
    For i = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If .Cells(i, 2).Value = Sheets("Sheet1").Range("A15").Value Then .Rows(i).Delete
    Next i
End With

 

[csilabgirl]

VoG,

I tried your code, but nothing happened. I didnt get a debug or anything, just nothing happened. Right now I have the code in a module. Is that ok?

 

[VoG]

The code should go in a regular module.

Should the code be looking for an exact match with A15 or cells that contain A15's value.

Note that I edited my code a couple of times after posting originally so you might want to check that you have the version that appears now.

 

[csilabgirl]

Yes, I do have that code, I re-pasted it, just to make sure.

"Should the code be looking for an exact match with A15 or cells that contain A15's value."

That seems to be the same question to me, so I think I must not be explaining it correctly, sorry. The code should be looking for an exact Match with A15.

So for example, in cell A15, Sheet1 I have "11-11925". On Sheet2, in the B column (A column is empty), in cells B2:B17 I have "11-11924" and then in B18:B33 I have "11-11925". Since that value exactly matches what is in cell A15 on sheet one, I would want rows 18:33 deleted, on sheet2.

I hope I explained it better. Thank you for your time

 

[dannac]

It worked for me when I changed

For i = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

to

For i = .Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1

 

[VoG]

It isn't doing anything because column A is empty. try

Dim i As Long
With Sheets("Sheet2")
    For i = .Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
    If .Cells(i, 2).Value = Sheets("Sheet1").Range("A15").Value Then .Rows(i).Delete
    Next i
End With

 

[csilabgirl]

Thank you dannac and Vog, that was the problem, it works now.

 

[VoG]

You are welcome. Please always state things like columns (esp. A) being empty

 

[csilabgirl]

I will make sure to do that in the future. Thank you