Have a loop hit every other row instead of every row using the Step command.
Transcript of the video:
MrExcel podcast is sponsored by “Easy-XL”!
VBA And Macros chapter 5 - The Every Other Loop!
OK, 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're 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 it write is figure out where the last row is today.
FinalRow = Cells(Rows.Count).End(xlUp) , and figure out what row that is.
And then here's the regular loop, For i = 2 To FinalRow , each time through the loop we're going to go to row number i, and change the ColorIndex 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.
OK, now this would go through and color every single row, but what I want to do is, I want to have a color every other row.
So we're going to add a new bit to this for the next loop here, it's called Step, alright, so Step 2 would go through and color every other row.
Now let's try this, let's do Step 3, and see if we can color every third row, alright, and I'm actually going to run this slowly.
So I press the F8 key, and we'll hover over FinalRow, we see that there's a 145 rows in the data set today.
We're going to go from 2-145, so the first time through when I hover over i, 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.
Alright, so right there we've applied some green formatting to row 2, but now, when we do Next i, it jumped by a factor of 3, so instead of being 2, now i = 5.
You see that we've colored that line of code, alright, and this is just going to keep going through.
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 it's working, and you see that we've colored the 3rd line every time.
So a great variation on the For Next Loop is being able to step, not look at every number, but every 2nd number or 3rd number or 4th number.
Or if you're looking at weeks, every 7th row or, you know, the possibilities are endless.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
VBA And Macros chapter 5 - The Every Other Loop!
OK, 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're 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 it write is figure out where the last row is today.
FinalRow = Cells(Rows.Count).End(xlUp) , and figure out what row that is.
And then here's the regular loop, For i = 2 To FinalRow , each time through the loop we're going to go to row number i, and change the ColorIndex 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.
OK, now this would go through and color every single row, but what I want to do is, I want to have a color every other row.
So we're going to add a new bit to this for the next loop here, it's called Step, alright, so Step 2 would go through and color every other row.
Now let's try this, let's do Step 3, and see if we can color every third row, alright, and I'm actually going to run this slowly.
So I press the F8 key, and we'll hover over FinalRow, we see that there's a 145 rows in the data set today.
We're going to go from 2-145, so the first time through when I hover over i, 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.
Alright, so right there we've applied some green formatting to row 2, but now, when we do Next i, it jumped by a factor of 3, so instead of being 2, now i = 5.
You see that we've colored that line of code, alright, and this is just going to keep going through.
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 it's working, and you see that we've colored the 3rd line every time.
So a great variation on the For Next Loop is being able to step, not look at every number, but every 2nd number or 3rd number or 4th number.
Or if you're looking at weeks, every 7th row or, you know, the possibilities are endless.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
