PDA

View Full Version : [SOLVED] Find the last cell in a column conditionally formatted



pcarmour
06-27-2014, 06:49 AM
Hi,

I have a large database in which I have conditionally formatted duplicate post codes in column C and sorted them to the top of the sheet.
I now want to delete all rows that do not have the duplicate formatting, IE all cells below row 4271.
please see attached.

11877

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

Any help will be very much appreciated.

snb
06-27-2014, 07:23 AM
Sub M_snb()
Range(Cells([min(if(countif($C$2:$C$14000,C2:C14000)=1,row(C2:C14000),20E3))], 3), Cells(Rows.Count, 3).End(xlUp)).EntireRow.Delete
End Sub


NB. it takes a while.

pcarmour
06-27-2014, 07:55 AM
Hi SNB,
Thank you again for replying so promptly to my thread.
I've run your code twice but I'm sorry to say it deletes all rows, I think this may possibly be due to different colour codes on our machines.

I'm about to leave my office now but will try again on my home PC this evening.

snb
06-27-2014, 08:03 AM
The code checks for uniques in column C starting from C2, because the columnlabel is always unique.
I used the file you uploaded. You should test in that one too.

pcarmour
06-27-2014, 08:11 AM
Hi SNB,
You are right, running the code on the test sheet deletes the required rows, I will just need to adapt now to the full version, thank you.

pcarmour
06-27-2014, 12:42 PM
Hi SNB,

Thank you for your time and effort with this thread.

I do now have your code working but as you quite rightly point out it does take some time.
I was rather thinking along the lines of finding the last conditionally formatted cell offsetting plus one row then selecting the range to the last row. Once selected I can delete entire rows in the range which I would have thought might be quicker.

What do you think?