Macro Help Needed: Comparing cell values and deleting rows

  • Thread starter Thread starter clint.sylvestre
  • Start date Start date
C

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!
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top