PDA

View Full Version : Urgent VBA Help Required!!



willo1232
09-30-2010, 06:57 AM
Hi,

:help I need a macro to do the following function:help :

Check the data in one column. If that data equals 0 then look in a different column containing new data and check to see if that data equals the line above or the line below.
If no then leave.
If yes then delete the line that contained the 0 in the first column checked.

I have to date this:

Code:
Option Explicit

Sub DeleteRows()
Dim LastRow As Long
Dim i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, "A").Value = 0 Then
If Cells(i - 1, "B").Value = Cells(i, "B").Value Or _
Cells(i + 1, "B").Value = Cells(i, "B").Value Then
Cells(i, "A").EntireRow.Delete
End If
End If
Next i
End Sub

Unfortunately, this isn't working, as the lines aren't being deleted, and maybe not even identified as incorrect.:dunno

Please, Please, Please can someone help!!:help

Thanks,

Bob Phillips
09-30-2010, 09:16 AM
What's wrong with it, it looks fine to me? How does it not work?

MeiR_ct
09-30-2010, 11:10 PM
I've just tested your code in my created sheet, worked great.
When it comes to deletion loops, the first thing I suspect is logic, but your code already made wisely and runs backwards on the lines, so the line indexes remain correct.

I wanna believe you have experience with vba, but if not: Did you put the code inside the specific sheet's object on VB Editor?
You must put it there and not in "ThisWorkbook" or in a module, since the code doesn't have any reference to the desired sheet.

When you check it, please update us here if it was the issue or not.

Hope I've helped,
Meir.

Simon Lloyd
09-30-2010, 11:51 PM
..... Did you put the code inside the specific sheet's object on VB Editor?
You must put it there and not in "ThisWorkbook" or in a module, since the code doesn't have any reference to the desired sheet.

When you check it, please update us here if it was the issue or not.

Hope I've helped,
Meir.Meir, sorry but your instruction is incorrect, the code is NOT worksheet event code it should reside in a standard module (Alt+F11, I, M) and the code does work as you state, if Willo is still having a problem he/she will have to upload a workbook that they are having trouble with :)

MeiR_ct
10-01-2010, 12:04 AM
Heh, okay.
As you can see in my stats, I'm a beginner and not a helper.
I'll suggest myself to keep learning and gaining some more experience, before helping others :)

mohanvijay
10-02-2010, 03:07 AM
i think there is some upper or lower case problem or cells data may contain spaces try this



If UCase(Trim(Cells(i - 1, "B").Value)) = UCase(Trim(Cells(i, "B").Value)) Or _
UCase(Trim(Cells(i + 1, "B").Value)) = UCase(Trim(Cells(i, "B").Value)) Then
Cells(i, "A").EntireRow.Delete
End If