Delete blank highlighted rows after filtering it based on colour Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!Select non-blank rows in spreadsheetHow to edit a macro in excel 2010 so that the filter criteria is: not equal to blanks?Excel Macro to concatenate cells and shift overHighlight cells based on 10-15 values and post alternativesExcel VBA Insert/delete Row in SheetRun a macro after perform a filter in VBA (Excel)Hightlight entire row if two columns match and are not blank - Excel VBASelect data based on a criteria then copy rows based that match to a certain criteraSelect all rows and columns that have data then filter with multiple criteriaExcel VBA insert fails after rows have been deleted on worksheet
Is grep documentation wrong?
Is the Standard Deduction better than Itemized when both are the same amount?
Is there such thing as an Availability Group failover trigger?
Circuit to "zoom in" on mV fluctuations of a DC signal?
Extracting terms with certain heads in a function
How do I find out the mythology and history of my Fortress?
Can you use the Shield Master feat to shove someone before you make an attack by using a Readied action?
Why are there no cargo aircraft with "flying wing" design?
What is homebrew?
Trademark violation for app?
When a candle burns, why does the top of wick glow if bottom of flame is hottest?
How do I make this wiring inside cabinet safer? (Pic)
Do wooden building fires get hotter than 600°C?
Delete nth line from bottom
2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?
Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?
What do you call the main part of a joke?
8 Prisoners wearing hats
What would be the ideal power source for a cybernetic eye?
Why wasn't DOSKEY integrated with COMMAND.COM?
Is "Reachable Object" really an NP-complete problem?
Irreducible of finite Krull dimension implies quasi-compact?
How to react to hostile behavior from a senior developer?
Declining "dulcis" in context
Delete blank highlighted rows after filtering it based on colour
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
Data science time! April 2019 and salary with experience
The Ask Question Wizard is Live!Select non-blank rows in spreadsheetHow to edit a macro in excel 2010 so that the filter criteria is: not equal to blanks?Excel Macro to concatenate cells and shift overHighlight cells based on 10-15 values and post alternativesExcel VBA Insert/delete Row in SheetRun a macro after perform a filter in VBA (Excel)Hightlight entire row if two columns match and are not blank - Excel VBASelect data based on a criteria then copy rows based that match to a certain criteraSelect all rows and columns that have data then filter with multiple criteriaExcel VBA insert fails after rows have been deleted on worksheet
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a dataset which have quite a few blank highlighted rows.
I already recorded and got the script to help me delete the desired rows.
However as macros only recorded what you select, in the code it gives the row number i selected. Now this works if the row number always remains constant.
As data order can change now and then, i would like to know if rather that reference to a particular row the script would just automatically select the immediate row after the header.
This is the code. For the line 'rows(5:5).Select', the row number can keep changing.
ActiveSheet.Range("$A$1:$O$2000").AutoFilter Field:=15, Criteria1:=RGB(180, _
180, 191), Operator:=xlFilterCellColor
rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
add a comment |
I have a dataset which have quite a few blank highlighted rows.
I already recorded and got the script to help me delete the desired rows.
However as macros only recorded what you select, in the code it gives the row number i selected. Now this works if the row number always remains constant.
As data order can change now and then, i would like to know if rather that reference to a particular row the script would just automatically select the immediate row after the header.
This is the code. For the line 'rows(5:5).Select', the row number can keep changing.
ActiveSheet.Range("$A$1:$O$2000").AutoFilter Field:=15, Criteria1:=RGB(180, _
180, 191), Operator:=xlFilterCellColor
rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
What do you exactly need to delete? what is the criteria for deleting rows?
– Damian
Mar 8 at 18:48
The criteria is the cell color in that column which is in the code as RGB. unfortunately the color in the sheet is not matching the 56 color codes for excel hence had to use RGB.
– Hussain Mithaiwala
Mar 8 at 18:50
add a comment |
I have a dataset which have quite a few blank highlighted rows.
I already recorded and got the script to help me delete the desired rows.
However as macros only recorded what you select, in the code it gives the row number i selected. Now this works if the row number always remains constant.
As data order can change now and then, i would like to know if rather that reference to a particular row the script would just automatically select the immediate row after the header.
This is the code. For the line 'rows(5:5).Select', the row number can keep changing.
ActiveSheet.Range("$A$1:$O$2000").AutoFilter Field:=15, Criteria1:=RGB(180, _
180, 191), Operator:=xlFilterCellColor
rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
I have a dataset which have quite a few blank highlighted rows.
I already recorded and got the script to help me delete the desired rows.
However as macros only recorded what you select, in the code it gives the row number i selected. Now this works if the row number always remains constant.
As data order can change now and then, i would like to know if rather that reference to a particular row the script would just automatically select the immediate row after the header.
This is the code. For the line 'rows(5:5).Select', the row number can keep changing.
ActiveSheet.Range("$A$1:$O$2000").AutoFilter Field:=15, Criteria1:=RGB(180, _
180, 191), Operator:=xlFilterCellColor
rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
excel vba
excel vba
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
edited Mar 11 at 13:26
Pᴇʜ
25.3k63052
25.3k63052
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
asked Mar 8 at 18:45
Hussain MithaiwalaHussain Mithaiwala
92
92
What do you exactly need to delete? what is the criteria for deleting rows?
– Damian
Mar 8 at 18:48
The criteria is the cell color in that column which is in the code as RGB. unfortunately the color in the sheet is not matching the 56 color codes for excel hence had to use RGB.
– Hussain Mithaiwala
Mar 8 at 18:50
add a comment |
What do you exactly need to delete? what is the criteria for deleting rows?
– Damian
Mar 8 at 18:48
The criteria is the cell color in that column which is in the code as RGB. unfortunately the color in the sheet is not matching the 56 color codes for excel hence had to use RGB.
– Hussain Mithaiwala
Mar 8 at 18:50
What do you exactly need to delete? what is the criteria for deleting rows?
– Damian
Mar 8 at 18:48
What do you exactly need to delete? what is the criteria for deleting rows?
– Damian
Mar 8 at 18:48
The criteria is the cell color in that column which is in the code as RGB. unfortunately the color in the sheet is not matching the 56 color codes for excel hence had to use RGB.
– Hussain Mithaiwala
Mar 8 at 18:50
The criteria is the cell color in that column which is in the code as RGB. unfortunately the color in the sheet is not matching the 56 color codes for excel hence had to use RGB.
– Hussain Mithaiwala
Mar 8 at 18:50
add a comment |
1 Answer
1
active
oldest
votes
Assuming the blank rows are the ones that are showing after filter, this will work.
With ActiveSheet.Range("$A$1:$O$2000")
.AutoFilter Field:=15, Criteria1:=RGB(180,180, 191), Operator:=xlFilterCellColor
On Error Resume Next 'only using this line in case there are no blank rows after filter
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error Go To 0 'and now turn error reporting back on so any other errors fire as needed
End With
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
1
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55069230%2fdelete-blank-highlighted-rows-after-filtering-it-based-on-colour%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assuming the blank rows are the ones that are showing after filter, this will work.
With ActiveSheet.Range("$A$1:$O$2000")
.AutoFilter Field:=15, Criteria1:=RGB(180,180, 191), Operator:=xlFilterCellColor
On Error Resume Next 'only using this line in case there are no blank rows after filter
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error Go To 0 'and now turn error reporting back on so any other errors fire as needed
End With
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
1
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
add a comment |
Assuming the blank rows are the ones that are showing after filter, this will work.
With ActiveSheet.Range("$A$1:$O$2000")
.AutoFilter Field:=15, Criteria1:=RGB(180,180, 191), Operator:=xlFilterCellColor
On Error Resume Next 'only using this line in case there are no blank rows after filter
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error Go To 0 'and now turn error reporting back on so any other errors fire as needed
End With
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
1
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
add a comment |
Assuming the blank rows are the ones that are showing after filter, this will work.
With ActiveSheet.Range("$A$1:$O$2000")
.AutoFilter Field:=15, Criteria1:=RGB(180,180, 191), Operator:=xlFilterCellColor
On Error Resume Next 'only using this line in case there are no blank rows after filter
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error Go To 0 'and now turn error reporting back on so any other errors fire as needed
End With
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
Assuming the blank rows are the ones that are showing after filter, this will work.
With ActiveSheet.Range("$A$1:$O$2000")
.AutoFilter Field:=15, Criteria1:=RGB(180,180, 191), Operator:=xlFilterCellColor
On Error Resume Next 'only using this line in case there are no blank rows after filter
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error Go To 0 'and now turn error reporting back on so any other errors fire as needed
End With
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
answered Mar 8 at 18:49
Scott HoltzmanScott Holtzman
23.6k62753
23.6k62753
1
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
add a comment |
1
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
1
1
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
Scott, Thanks! i thought of something like this but assumed that it wouldn't recognize blank cells as visible. i changed the range from A1 to A2 to keep my header row intact.
– Hussain Mithaiwala
Mar 8 at 19:01
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55069230%2fdelete-blank-highlighted-rows-after-filtering-it-based-on-colour%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What do you exactly need to delete? what is the criteria for deleting rows?
– Damian
Mar 8 at 18:48
The criteria is the cell color in that column which is in the code as RGB. unfortunately the color in the sheet is not matching the 56 color codes for excel hence had to use RGB.
– Hussain Mithaiwala
Mar 8 at 18:50