Macro Help Needed: Comparing cell values and deleting rows

[clint.sylvestre]

Greetings Excel Pros!
I am new to Macro writing... I know what I want to do, but given the
time constraints, I think this medium is the quickest solution to my
question. I don't have enough time to spend researching and studying
VBA syntax and built-in functions.

Here's the problem to be solved with a Macro: I have a large file (~
25,000 row entries) but upon filtering, I can isolate blocks of about
60 rows (not concurrent, i.e. rows 1, 2, 13, 18, 20, 25, 31 etc.).
Within this sub-set I want to delete rows based on a comparison task.
Columns A-M are in use, and rows 1 & 2 are frozen because I'm using
them as header rows.

I will sort Column "L" so that its entries contain values Lot 1 - Lot 4
in ascending order. Column "D" contains the values to be compared with
all row entries where Column "L" contains Lot 1 - Lot 3. The main idea
is to delete duplicates (based only on value of Column "D" entry) for
on Lot 4 entries. Additionally, when conducting the check, only the
first THREE characters of each Column "D" cell should be compared. Only
the first FIVE characters of each Column "L" cell should be compared. I
know that in the spreadsheet there is a "=LEFT(A:A, 5)" function for
example. Can this be used here somehow?

Here's the pseudocode I came up with...

'Check Column L for first occurrence of "Lot 4" in order to assign
reference

For i = first_row_of_column_L To last_entry_found_in_column_L Step +1
If current_cell = "Lot 4" Then
init_Lot4_Ref = current_cell 'Break at this point, our reference
is set!

'Else, check next cell
Next i


'Now that we have a reference, check Column D to delete duplicate rows

For j = init_Lot4_Ref to last_entry_found_in_column_L Step +1
For k = first_row to j Step +1
If Cells(k, "D").Value = Cells(j, "D").Value Then
Rows(k).EntireRow.Delete
Next k
Next j



*** Remember that when checking Column L entry, only check first FIVE
characters (some entries are listed "Lot 4 special" but this should be
treated like "Lot 4"). When checking Column D entry, only check first
THREE characters (some entries are listed "225(NEW)" but this should be
treated like "225").


Thanks to whomever can help!

 

[JLatham]

In VBA there are similar functions to LEFT() and RIGHT() used on a worksheet.
Keep in mind that within VBA, case is evaluated, so "Lot 4" <> "LOT 4", this
example code would examine the first 5 characters of each of 2 cells, making
sure that leading spaces were removed and that both were in UPPER case before
making the comparison:

If UCase(Left(Trim(Cells(k, "D")), 5)) = UCase(Left(Trim(Cells(j, "D")), 5))
Then
....
use 3 instead of 5 for the other test.
Hope this helps you get moving again.