software

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 quot;B (n)quot;,
read the value,
go to cell quot;B (n 1)quot;
if cell quot;B (n)quot;= cell quot;B (n 1)quot;
then erase line (n 1)
Implement n = n 1
Start again
if cell quot;B (n)quot; different to cell quot;B (n 1)quot;
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 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, quot;Bquot;).End(xlUp).Row

For iRow = LastRow To FirstRow 1 Step -1
If .Cells(iRow, quot;Bquot;).Value = .Cells(iRow - 1, quot;Bquot;).Value Then
'it's a duplicate
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
CamiIRE wrote:
gt;
gt; Hi!
gt; I would like to make a selection through a complete data set by selecting
gt; lines based on the script in on cell of each line. for example:
gt;
gt; Line/Column: A B C
gt; 1 X1 2 Y1
gt; 2 X2 2 Y2
gt; 3 X3 5 Y3
gt; 4 X4 7 Y4
gt;
gt; Say n: the line number, stating at n=1
gt;
gt; I want the macro to start inside cell quot;B (n)quot;,
gt; read the value,
gt; go to cell quot;B (n 1)quot;
gt; if cell quot;B (n)quot;= cell quot;B (n 1)quot;
gt; then erase line (n 1)
gt; Implement n = n 1
gt; Start again
gt; if cell quot;B (n)quot; different to cell quot;B (n 1)quot;
gt; Implement n = n 1
gt; Start again
gt;
gt; The result would be here taht line 1,3 and 4 are kept while the 2nd one is
gt; erased.
gt;
gt; It would be great if you can help me!
gt;
gt; Many thanks

--

Dave Peterson

Hi Dave,

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

Thanks again

CamiIRE

quot;Dave Petersonquot; wrote:

gt; So you want to delete rows that are duplicates of the row above it (based on the
gt; value in column B)?
gt;
gt; It's easier if you start at the bottom and work your way up the rows.
gt;
gt; Option Explicit
gt; Sub testme02()
gt; Dim LastRow As Long
gt; Dim FirstRow As Long
gt; Dim iRow As Long
gt; Dim wks As Worksheet
gt;
gt; Set wks = ActiveSheet
gt;
gt; With wks
gt; FirstRow = 1 'no header rows
gt; LastRow = .Cells(.Rows.Count, quot;Bquot;).End(xlUp).Row
gt;
gt; For iRow = LastRow To FirstRow 1 Step -1
gt; If .Cells(iRow, quot;Bquot;).Value = .Cells(iRow - 1, quot;Bquot;).Value Then
gt; 'it's a duplicate
gt; .Rows(iRow).Delete
gt; End If
gt; Next iRow
gt; End With
gt; End Sub
gt;
gt;
gt;
gt; CamiIRE wrote:
gt; gt;
gt; gt; Hi!
gt; gt; I would like to make a selection through a complete data set by selecting
gt; gt; lines based on the script in on cell of each line. for example:
gt; gt;
gt; gt; Line/Column: A B C
gt; gt; 1 X1 2 Y1
gt; gt; 2 X2 2 Y2
gt; gt; 3 X3 5 Y3
gt; gt; 4 X4 7 Y4
gt; gt;
gt; gt; Say n: the line number, stating at n=1
gt; gt;
gt; gt; I want the macro to start inside cell quot;B (n)quot;,
gt; gt; read the value,
gt; gt; go to cell quot;B (n 1)quot;
gt; gt; if cell quot;B (n)quot;= cell quot;B (n 1)quot;
gt; gt; then erase line (n 1)
gt; gt; Implement n = n 1
gt; gt; Start again
gt; gt; if cell quot;B (n)quot; different to cell quot;B (n 1)quot;
gt; gt; Implement n = n 1
gt; gt; Start again
gt; gt;
gt; gt; The result would be here taht line 1,3 and 4 are kept while the 2nd one is
gt; gt; erased.
gt; gt;
gt; gt; It would be great if you can help me!
gt; gt;
gt; gt; Many thanks
gt;
gt; --
gt;
gt; Dave Peterson
gt;