Line selection from a cell script through a VBA macro

[CamiIRE]

Hi!
I would like to make a selection through a complete data set by selecting
lines based on the script in on cell of each line. for example:

Line/Column: A B C
1 X1 2 Y1
2 X2 2 Y2
3 X3 5 Y3
4 X4 7 Y4

Say n: the line number, stating at n=1

I want the macro to start inside cell "B (n)",
read the value,
go to cell "B (n+1)"
if cell "B (n)"= cell "B (n+1)"
then erase line (n+1)
Implement n = n+1
Start again
if cell "B (n)" different to cell "B (n+1)"
Implement n = n+1
Start again

The result would be here taht line 1,3 and 4 are kept while the 2nd one is
erased.

It would be great if you can help me!

Many thanks

 

[Dave Peterson]

So you want to delete rows that are duplicates of the row above it (based on the
value in column B)?

It's easier if you start at the bottom and work your way up the rows.

Option Explicit
Sub testme02()
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub

 

[CamiIRE]

Hi Dave,

Thank you very much, it just worked out perfectly and saved me so much time!

Thanks again

CamiIRE