Line selection from a cell script through a VBA macro
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 :) |
Line selection from a cell script through a VBA macro
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 wrote: 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 |
Line selection from a cell script through a VBA macro
Hi Dave,
Thank you very much, it just worked out perfectly and saved me so much time! Thanks again CamiIRE "Dave Peterson" wrote: 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 wrote: 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 |
All times are GMT +1. The time now is 11:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com