In this episode [Episode #1224], learn how to have a loop hit every other row instead of every row using the Step command.
...This episode is the video podcast companion to the book, "VBA And Macros: Microsoft Excel 2010", from Bill Jelen
...This episode is the video podcast companion to the book, "VBA And Macros: Microsoft Excel 2010", from Bill Jelen
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
VBA and Macros chapter 5 - Every Other Loop.
Okay, so we've been talking about loops in the book and I want to show you something cool here.
Usually we loop to look at every single row so for, you know, I Equals to the final row is something we want to do but let's say that we want to apply green bar formatting the old way not using table functionality.
I know the table functionality adds green bar formatting.
I just want to do with the old way and so I want to go through and look at every other row.
Row 2, row 4, row 6, row 8 and an easy way to do that, we are going to switch over to VBA here and take a look at the code.
So first thing we do, first line of code in every macro that I write is figure out where the last row is today.
FinalRow = CellsRows.Count and XL up to figure out what row that is and then here's the regular loop for I equals to final row.
Each time through the loop we're going to go to row number I and change the color index to 35.
This underscore here is a continuation character.
Actually it's a space and then underscore is the continuation character.
I did that to make it fit here in the size of the iPod window.
Okay, now this would go through and color every single row.
But what I wanna to do is I wanna have a color every other row.
So we're going to add a new bit to this for next loop here.
It's called step, alright?
So step 2 would go through and color every other row.
Let's try this, let's do step 3 and see if we can color every third row, all right?
And I'm actually going to run this slowly, so I press the F8 key.
And will hover over final row we see that there's a 145 rows in the data set today.
We're going to go from 2 to 145.
So the first time through when I hover over I, you see that we're about to Format Row 2.
I'll Run that line of code and we'll switch back to excel and see that it happened.
All right, so right that we've applied some green formatting to row 2 but now when we do next I it jumped, it jumped by a factor of 3, so instead of being 2 now I is equal to five.
You see that we colored that line of code, all right?
And this is just going to keep going through.
We'll run it a few more times, and you'll see that we're coloring more and more lines of code.
I'll just let it run now run the rest of the way through now that I see that's working.
And you see that we've colored the third line every time.
So a great variation on the for next loop is being able to step not and look at every number, but every second number or third number or fourth number or if you're looking at weeks every seventh row or you know, the possibilities are endless.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
VBA and Macros chapter 5 - Every Other Loop.
Okay, so we've been talking about loops in the book and I want to show you something cool here.
Usually we loop to look at every single row so for, you know, I Equals to the final row is something we want to do but let's say that we want to apply green bar formatting the old way not using table functionality.
I know the table functionality adds green bar formatting.
I just want to do with the old way and so I want to go through and look at every other row.
Row 2, row 4, row 6, row 8 and an easy way to do that, we are going to switch over to VBA here and take a look at the code.
So first thing we do, first line of code in every macro that I write is figure out where the last row is today.
FinalRow = CellsRows.Count and XL up to figure out what row that is and then here's the regular loop for I equals to final row.
Each time through the loop we're going to go to row number I and change the color index to 35.
This underscore here is a continuation character.
Actually it's a space and then underscore is the continuation character.
I did that to make it fit here in the size of the iPod window.
Okay, now this would go through and color every single row.
But what I wanna to do is I wanna have a color every other row.
So we're going to add a new bit to this for next loop here.
It's called step, alright?
So step 2 would go through and color every other row.
Let's try this, let's do step 3 and see if we can color every third row, all right?
And I'm actually going to run this slowly, so I press the F8 key.
And will hover over final row we see that there's a 145 rows in the data set today.
We're going to go from 2 to 145.
So the first time through when I hover over I, you see that we're about to Format Row 2.
I'll Run that line of code and we'll switch back to excel and see that it happened.
All right, so right that we've applied some green formatting to row 2 but now when we do next I it jumped, it jumped by a factor of 3, so instead of being 2 now I is equal to five.
You see that we colored that line of code, all right?
And this is just going to keep going through.
We'll run it a few more times, and you'll see that we're coloring more and more lines of code.
I'll just let it run now run the rest of the way through now that I see that's working.
And you see that we've colored the third line every time.
So a great variation on the for next loop is being able to step not and look at every number, but every second number or third number or fourth number or if you're looking at weeks every seventh row or you know, the possibilities are endless.
I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
