Looping Function for Matching Rows

[ExcelGuy160]

Hello all,

Need a little help converting this IF statement in VB to a loop:

Please Login or Register  to view this content.

A2 B2 C2 D2 E2 F2 G2
A3 B3 C3 D3 E3 F3 G3
becomes:
A2 B2 C2 D2D3 E2 F2 G2
A3 B3 C3 D3 E3 F3 G3

Basically, the macro goes through each row and the row below it. If col A, B, C and E, F, G match, then it combines the contents of col D, then highlights the row below it (marked as a duplicate).

I am 1) trying to write this into a loop to go through as many as 3000 rows and 2) simply the IF statement when possible.

Any help on this would be greatly appreciated! Thanks!

[MarvinP]

Hi ExcelGuy160

Here is how to combine all those ANDs and makes it more readable. I only did the top for you. You can figure out the second one.

Please Login or Register  to view this content.

[MarvinP]

Another method using For Next loop and cells instead of range would be: (Top only)

Please Login or Register  to view this content.

[MarvinP]

This would also work as the above but a little simpler?

Please Login or Register  to view this content.

[ExcelGuy160]

Thanks so much for your help! I guess my question is: how do I make this a loop to go through as many as 3K lines on the worksheet. Will I have to code each line like this in VB or is there a shorter way write a looping macro?

[Marcol]

Is it possible for non-consecutive rows to match, say rows 5, 200, 603, etc, or are you just compairing consecutive rows?

[ExcelGuy160]

There is a possibilty of a group of rows to match that are consecutive, but nothing non-consecutive like 5, 200, etc. For example:
A2 B2 C2 D2 E2 F2 G2
A3 B3 C3 D3 E3 F3 G3
A4 B4 C4 D4 E4 F4 G4
becomes:
A2 B2 C2 D2D3D4 E2 F2 G2
A3 B3 C3 D3 E3 F3 G3
A4 B4 C4 D4 E4 F4 G4

[Marcol]

Try this

Please Login or Register  to view this content.

This should clear all duplicates and add the contents of the deleted row (Col D) to the remaining one.

Because it filters out all duplicate rows it need only step through rows of data and not every cell

If you need to keep the deleted rows then change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hope this helps

[ExcelGuy160]

Thank you for your help! However, the code didn't work for me - didn't seem to make any changes to the worksheet. Everything in the rows needs to match for the row to be deleted except col D. the contents of col D need to be combined with the col D cell above it, then the row deleted if A, B, C, E, F, and G are a match. Does that make sense?

[snb]

Please Login or Register  to view this content.

[Marcol]

You would do best to post a sample workbook showing Before and After.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers
.

[ExcelGuy160]

SNB, thank you! that worked great! Much appreciated