Related:
- How to delete rows in excel with conditional formatting
- Conditionally deleting rows in excel 2007 ✓ - Forum - Excel
- Conditionally delete rows in excel - How-To - Excel
- Macros to Delete Rows in Excel 2007 ✓ - Forum - Programming
- Deleteing Rows in excel ✓ - Forum - Excel
- Excel apply conditional formatting to entire column ✓ - Forum - Excel
3 replies
TrowaD
- Posts
- 2665
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 12, 2020
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
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
- 2665
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 12, 2020
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
