Previous Topic Next Topic

Delete Rows with no conditional formatting highlights

Closed
oldschool1234 Posts 2 Registration date Sunday October 5, 2014 Status Member Last seen October 7, 2014 - Oct 5, 2014 at 11:19 PM
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 - Oct 9, 2014 at 11:31 AM
Hi everyone!

I have a quick query regarding tidying-up some worksheets I generate regularly that I've been unable to find a solution for despite searching for several days on this and many other websites.

I have three separate worksheets in the same workbook, which contain various columns of data. Using conditional formatting I have highlighted one or more cells in several columns in each worksheet (but not the same columns across all three), and I now just want to either delete or hide all rows that have no cells highlighted by the conditional formatting.

The worksheet details are as follows:

Worksheet 1:
Columns G, H+ I may have conditional formatting highlights.


Worksheet 2:
Columns G, H+ I may have conditional formatting highlights.


Worksheet 3:
Columns O+ P may have conditional formatting highlights.


All code examples I've tried don't work (they delete all rows!!) as I understand conditional formatting doesn't quite work in the same way as manual highlighting (correct me if I'm wrong), so I would greatly appreciate any help anyone can offer.

I'm happy with a bit of code I can customise for each sheet as it's only for three and that number will never change, or any other manual steps I can record as a Macro.

I've just suddenly had to become an Excel power user but have virtually no experience beyond basic lists etc., however I've created several macros and web queries successfully this weekend (using some of the solutions on your website) which will now save me lots of time in future, so this is now the final hurdle to overcome and I despise unfinished tasks!!

Thanks in advance to anyone willing to help and keep up the good work with the site.


All the best,
Mike.
Related:

3 replies

Answer 1 / 3
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Oct 6, 2014 at 11:59 AM
Hi Mike,

You are right about the difference in CF and manual colouring a cell. CF colouring isn't detected by VBA.

But CF uses a condition to colour cells, the same condition can be used to find those CF coloured cells again.

You said you would be happy with a bit of code; so try to work with this: 

Sub AlterMe()
For Each cell In Range("G1:H10")
    If cell = "Your CF condition here" Then cell.EntireRow.Delete
Next cell
End Sub


Let us know how this works out for you.

Best regards,
Trowa
0
Answer 2 / 3
oldschool1234 Posts 2 Registration date Sunday October 5, 2014 Status Member Last seen October 7, 2014
Oct 7, 2014 at 04:13 PM
Trowa thanks for that suggestion much appreciated, I've had a look into and it seems as because I'm using Excel 2010 it may be trickier than I hoped-found this snippet below after some reserach and many attempts to do what you suggested (reminder I'm new to this and still getting my head around the coding side of things):

"In Excel 2010, the format equations for every cell are IDENTICAL, a range is defined for all the cells, and Excel internally does the "redefinitions". The above macro "falsely" sees identical cell references for every "cell" in the (Excel 2010) defined range so all "permanent" formatting is made based on the original cell references. Even using RC cell references in the conditional formating equations doesn't yield the desired "redefinitons" for each cell."

I did find an Excel add-in that would do what I want (remove rows NOT containing cells with conditional formatting (in this case two font colours) but it only works with Excel 2003.

I have that on another macine so will test that, but for now I'm not sure if there's a solution to this in Excel 2010.

To elaborate I have data with 2 or 3 columns out of 12 containing a percentage in the format 99% for example, and across 3 separate worksheets I wish to remove (delete or hide makes no odds) those rows that have not met the criteria for the conditional formatting.

Note: The conditional formatting is different on the 3 worksheets, the worksheets and data are all compiled and formatted via web query/macros.

As said I will revisit the add-in via Excel 2003 and see what that does but I will devote sometime to continue looking for a solution in Excel 2010 as that's preferred.

If I've completely missed something as a newbie feel free to correct me, and thanks again for taking time to help.

Cheers,
Mike.
0
Answer 3 / 3
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Oct 9, 2014 at 11:31 AM
Hi Mike,

Could you provide more details on your CF; what is the formula/criteria you use and to which range it is applied.

As mentioned in your snippet, it could indeed complicate things, but that depends on your CF criteria.

If, for example, you have colored the cells where the percentage is bigger the 50%, then one could easily loop through multiple columns deleting rows which contains cells with percentages bigger the 50%.

If you have colored the cells depending on a cell in the same row (i.e. if A1>B1 then color A1, if A2>B2 then color A2) then it is still possible, only the code will be more complex.

Awaiting your info.

Best regards,
Trowa
0