Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 :) |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Expansion | Excel Worksheet Functions | |||
Macro for cell selection starting with Last Cell | Excel Worksheet Functions | |||
Macro help - copy a cell down | Excel Discussion (Misc queries) | |||
macro help | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |