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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Oct 9, 2014 at 11:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Oct 9, 2014 at 11:31 AM
Related:
- Conditionally delete cells in excel
- Excel date format dd.mm.yyyy - Guide
- Screenshot in excel - Guide
- Number to words in excel without vba - Guide
- How to delete whatsapp account without login - Guide
- How to delete whatsapp account without phone - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 16, 2023
549
Oct 6, 2014 at 11:59 AM
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:
Let us know how this works out for you.
Best regards,
Trowa
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
oldschool1234
Posts
2
Registration date
Sunday October 5, 2014
Status
Member
Last seen
October 7, 2014
Oct 7, 2014 at 04:13 PM
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.
"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.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 16, 2023
549
Oct 9, 2014 at 11:31 AM
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
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
