Hi there!

It's been about a billion years since I worked with Excel
macros, I think that part of my brain has atrophied! I
have some inventory data in an Excel spreadsheet, each
item in the inventory is in a row of cells.

There are multiple records for some items. If I sort the
data block by part number first, then "date entered"
second, I get the newest listing for each item first. I
want to delete subsequent listings for the same item.
There are about 18,000 rows in this spreadsheet, so this
seems like a job tailor-made for a tidy little macro.

Here's what I want to do in layman's terms.

1. Select the contiguous block of data
2. Sort the data first on column B (ascending) and second
column L(descending)
3. Place the cursor at the top of column B
4. Read the value of the cell under the cursor into a
register
5. Move the cursor down one cell
6. Does the cell under the cursor have the same
information as the comparison registier?
7. If it does delete the line from the spreadsheet
8. If it does not,read the value of the cell under the
cursor into a register
9. Go back to line 5, repeat

How can I make this happen in a macro? Excel 2002 by the
way.

Thanks for your time!

If you have done some programming before then the best way
would be to do exactly the steps that you said below,
whist using a record macro. Then go into the code an
change the hard cell references to variables that get
changed in a loop (that does step 5 - step 9 over and over
until it is finished you should probably look up "For
Method" if you forget the loop syntax
-----Original Message-----
Hi there!

It's been about a billion years since I worked with Excel
macros, I think that part of my brain has atrophied! I
have some inventory data in an Excel spreadsheet, each
item in the inventory is in a row of cells.

There are multiple records for some items. If I sort the
data block by part number first, then "date entered"
second, I get the newest listing for each item first. I
want to delete subsequent listings for the same item.
There are about 18,000 rows in this spreadsheet, so this
seems like a job tailor-made for a tidy little macro.

Here's what I want to do in layman's terms.

1. Select the contiguous block of data
2. Sort the data first on column B (ascending) and second
column L(descending)
3. Place the cursor at the top of column B
4. Read the value of the cell under the cursor into a
register
5. Move the cursor down one cell
6. Does the cell under the cursor have the same
information as the comparison registier?
7. If it does delete the line from the spreadsheet
8. If it does not,read the value of the cell under the
cursor into a register
9. Go back to line 5, repeat

How can I make this happen in a macro? Excel 2002 by the
way.

Thanks for your time!


.

I cannont remember much at all to tell you the truth, my
macro programming experience pre-dates VBA by quite a few
years! How do you declare variables?

-----Original Message-----
If you have done some programming before then the best
way
would be to do exactly the steps that you said below,
whist using a record macro. Then go into the code an
change the hard cell references to variables that get
changed in a loop (that does step 5 - step 9 over and
over
until it is finished you should probably look up "For
Method" if you forget the loop syntax
-----Original Message-----
Hi there!

It's been about a billion years since I worked with
Excel
macros, I think that part of my brain has atrophied! I
have some inventory data in an Excel spreadsheet, each
item in the inventory is in a row of cells.

There are multiple records for some items. If I sort
the
data block by part number first, then "date entered"
second, I get the newest listing for each item first.
I
want to delete subsequent listings for the same item.
There are about 18,000 rows in this spreadsheet, so
this
seems like a job tailor-made for a tidy little macro.

Here's what I want to do in layman's terms.

1. Select the contiguous block of data
2. Sort the data first on column B (ascending) and
second
column L(descending)
3. Place the cursor at the top of column B
4. Read the value of the cell under the cursor into a
register
5. Move the cursor down one cell
6. Does the cell under the cursor have the same
information as the comparison registier?
7. If it does delete the line from the spreadsheet
8. If it does not,read the value of the cell under the
cursor into a register
9. Go back to line 5, repeat

How can I make this happen in a macro? Excel 2002 by
the
way.

Thanks for your time!


.

.

Here is one way. Paste this into a new VBA module and see
if it works:

Sub Macro1()
Sheets("Sheet1").Select
Range("B1").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlDescending, Header:=xlGuess,
OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("B2").Select
Do While ActiveCell.Value < ""
If ActiveCell.Offset(1, 0).Text = ActiveCell.Text Then
ActiveCell.Offset(1, 0).EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

Regards
Kevin B

-----Original Message-----
Hi there!

It's been about a billion years since I worked with Excel
macros, I think that part of my brain has atrophied! I
have some inventory data in an Excel spreadsheet, each
item in the inventory is in a row of cells.

There are multiple records for some items. If I sort the
data block by part number first, then "date entered"
second, I get the newest listing for each item first. I
want to delete subsequent listings for the same item.
There are about 18,000 rows in this spreadsheet, so this
seems like a job tailor-made for a tidy little macro.

Here's what I want to do in layman's terms.

1. Select the contiguous block of data
2. Sort the data first on column B (ascending) and second
column L(descending)
3. Place the cursor at the top of column B
4. Read the value of the cell under the cursor into a
register
5. Move the cursor down one cell
6. Does the cell under the cursor have the same
information as the comparison registier?
7. If it does delete the line from the spreadsheet
8. If it does not,read the value of the cell under the
cursor into a register
9. Go back to line 5, repeat

How can I make this happen in a macro? Excel 2002 by the
way.

Thanks for your time!


.