Looking back to August 2011, a 'Learn Excel from MrExcel' Podcasts looked at how to Sum Colored Cells; Episode #1415 showed us how we could be obtaining the 'Sum' of the Red Rows. Today, in Episode #1687, Bill revisits that earlier Podcast to show us how to Count the Number of Rows containing Red Cells, not Sum the Cell Values.
For help adapting the code in this video to other situations (such as also making sure the cell contains "dog"), start a new topic at Excel Questions
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
And for more on VBA in Excel 2010, check out the book, "VBA And Macros: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
www.MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
For help adapting the code in this video to other situations (such as also making sure the cell contains "dog"), start a new topic at Excel Questions
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
And for more on VBA in Excel 2010, check out the book, "VBA And Macros: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
www.MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1687: Count Red Rows.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Yes, it's Friday. It should be a dueling Excel podcast-- it was my bad, came down here to Florida and I did not get the duel set up with Mike this week, but it'll be back next week.
So, here we're going back to Episode 1415, where I showed a little macro to count the red cells.
Actually, back then, it was sum the red cells.
But a whole discussion broke out on Youtube, someone wanted to know how to count the red cells.
Let's take a quick look at that.
I'll switch over to VBA-- Alt+F11-- and this was the tiny little Macro.
So, we have a function called CountRed, we initialize that to 0 For Each Cell In MyRange, if the color is 255-- that's the color red-- then CountRed = CountRed + 1.
Right?
Nice, simple little macro.
And then someone comes along and says, "Hey, well, no, I just want to count the rows that contain red-- if there's two red cells I want to count that as one".
And that becomes so much more difficult.
I will scroll down here, okay, so, CountRedRows.
Well, now we have to do all this pre-work to figure out what the FirstRow is, so MyRange.Rows(1)-- so whatever the first row is in the range, give me the row number.
And then RowCount = MyRange.Rows.Count, you know, so rows is a collection of all the rows in my range and this will tell me how many there are.
I had to do that just so that way I could do MyRange.Rows-- give me the last row and tell me what row it is.
Same thing for the columns here.
Then once I know that I have two loops, For i = FirstRow To LastRow and then j = FirstCol To LastCol-- so, as you can imagine, we're going through Row 2 or 3, Row 4, Row 5-- within each of those rows, we're going through Column 2, 3, 4, 5, and 6.
Check and see if the row-- or if the cell-- at the intersection of this row and this column is red, if it is, then yeah, we increment CountRedRows by 1.
But we also have to skip the rest of the columns in this row, so this is where "Exit For" comes in.
Exit For says, Hey, I know we have a For/Next loop here-- the For j = 2 to 6, I think, in this case-- but if we get to this Exit For inside the If statement, then just skip the rest of the columns in this loop.
Alright, so there's a good way to Exit the current loop: if I needed to Exit the i loop here, I'd have to set something-- some sort of a variable in weight-- til I got outside of the next j.
So Exit For is handy if you're trying to cancel the current loop.
It won't cancel the i loop, we're still just going to move on to the next row.
So, let's go and take a look.
So =CountRedRows of this whole range, and hopefully bring it less than 9 because there's a couple of [ Inaudible at 00:02:55 ].
So, that row has two red cells, this row has two red cells, and you get just the number of rows that contain red.
It sounds like a simple little change, doesn't it?
But it adds a lot of complexity to the macro, from that macro down to this one.
Alright, well, hey I want to thank you for stopping by.
Again, hey, for Macros, check out Tracy and my book, "VBA And Macros".
There's four editions: Excel 2003, 2007, 2010, or 2013.
Or if you like to watch videos, there's a live lessons Power Macros-- or Excel Macros-- in VBA.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1687: Count Red Rows.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Yes, it's Friday. It should be a dueling Excel podcast-- it was my bad, came down here to Florida and I did not get the duel set up with Mike this week, but it'll be back next week.
So, here we're going back to Episode 1415, where I showed a little macro to count the red cells.
Actually, back then, it was sum the red cells.
But a whole discussion broke out on Youtube, someone wanted to know how to count the red cells.
Let's take a quick look at that.
I'll switch over to VBA-- Alt+F11-- and this was the tiny little Macro.
So, we have a function called CountRed, we initialize that to 0 For Each Cell In MyRange, if the color is 255-- that's the color red-- then CountRed = CountRed + 1.
Right?
Nice, simple little macro.
And then someone comes along and says, "Hey, well, no, I just want to count the rows that contain red-- if there's two red cells I want to count that as one".
And that becomes so much more difficult.
I will scroll down here, okay, so, CountRedRows.
Well, now we have to do all this pre-work to figure out what the FirstRow is, so MyRange.Rows(1)-- so whatever the first row is in the range, give me the row number.
And then RowCount = MyRange.Rows.Count, you know, so rows is a collection of all the rows in my range and this will tell me how many there are.
I had to do that just so that way I could do MyRange.Rows-- give me the last row and tell me what row it is.
Same thing for the columns here.
Then once I know that I have two loops, For i = FirstRow To LastRow and then j = FirstCol To LastCol-- so, as you can imagine, we're going through Row 2 or 3, Row 4, Row 5-- within each of those rows, we're going through Column 2, 3, 4, 5, and 6.
Check and see if the row-- or if the cell-- at the intersection of this row and this column is red, if it is, then yeah, we increment CountRedRows by 1.
But we also have to skip the rest of the columns in this row, so this is where "Exit For" comes in.
Exit For says, Hey, I know we have a For/Next loop here-- the For j = 2 to 6, I think, in this case-- but if we get to this Exit For inside the If statement, then just skip the rest of the columns in this loop.
Alright, so there's a good way to Exit the current loop: if I needed to Exit the i loop here, I'd have to set something-- some sort of a variable in weight-- til I got outside of the next j.
So Exit For is handy if you're trying to cancel the current loop.
It won't cancel the i loop, we're still just going to move on to the next row.
So, let's go and take a look.
So =CountRedRows of this whole range, and hopefully bring it less than 9 because there's a couple of [ Inaudible at 00:02:55 ].
So, that row has two red cells, this row has two red cells, and you get just the number of rows that contain red.
It sounds like a simple little change, doesn't it?
But it adds a lot of complexity to the macro, from that macro down to this one.
Alright, well, hey I want to thank you for stopping by.
Again, hey, for Macros, check out Tracy and my book, "VBA And Macros".
There's four editions: Excel 2003, 2007, 2010, or 2013.
Or if you like to watch videos, there's a live lessons Power Macros-- or Excel Macros-- in VBA.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
