Kuldeep wants to select every third cell for 1500 cells
The macro code to union all 500 cells is too long and causes a compile error!
Solution: Build 1, blank, blank. Copy those three cells and paste to 1500 cells
Select the 1500 cells. Home, Find & Select, Go To Special, Constants to select every third cell
Then, one line of VBA to move the selection N rows or columns away.
Finally, a VBA Macro to do the whole thing
Cool Tip: If you use Ctrl+G for Go To dialog, it shows recently selected ranges.
Cool Tip: In the Go To dialog, hold down Shift to select from Current Cell to the Go To cell.
VBA Tip: Using UsedRegion to find the last row in the worksheet
The macro code to union all 500 cells is too long and causes a compile error!
Solution: Build 1, blank, blank. Copy those three cells and paste to 1500 cells
Select the 1500 cells. Home, Find & Select, Go To Special, Constants to select every third cell
Then, one line of VBA to move the selection N rows or columns away.
Finally, a VBA Macro to do the whole thing
Cool Tip: If you use Ctrl+G for Go To dialog, it shows recently selected ranges.
Cool Tip: In the Go To dialog, hold down Shift to select from Current Cell to the Go To cell.
VBA Tip: Using UsedRegion to find the last row in the worksheet
Transcript of the video:
Learn Excel From MrExcel, Podcast Episode 2108: Select Every Third Cell.
This is a doozy. Kuldeep sends in a question.
He says he has data in E24 to BEV24. That's 1500 cells.
He needs to select every third cell.
A note, the union macro is too long and generates errors.
What's he talking about, the union macro?
He's talking about, he's already tried this, HOWTOSELECTEVERYTHIRDCELL.
Record a little macro here.
Select this cell and then CONTROL keys, that cell, that cell, that cell, that cell, that cell.
I didn't go too far. I just want to see how they're recording the code.
Stop recording. ALT+F8.
HOWTOSELECTEVERYTHIRDCELL, EDIT, and this is really bad.
There's no magic way to select every third cell.
You just have to list all 500 cells in there, which would take longer than selecting the 500 cells, right?
Just pointless.
Pointless, pointless, pointless.
So that the macro recorder has no better way to do this.
So I said to Kuldeep, what we're going to have to do, the thing we have to leverage, is special cells.
I said, by any chance, is every third cell numeric and everything else is text?
No, he says.
Alright, so here's what I'm going to do.
I'm going to go to a brand new row and put a 1 and then nothing, nothing, and I'm going to copy these three cells, those three cells like that, and then what I’m going to do is I’m going to go to the end and select all of those cells.
Now, this is 1500 cells which is a multiple of 3, and paste, and what that's going to do for me is it's going to give me every third cell is going to be numeric, like that, and the beautiful thing is, what we can do then is, select from here, type BEV26, and I'll hold down the SHIFT key when I click OK, and that will select to the end, and then HOME, FIND & SELECT, GO TO SPECIAL, and I want the CONSTANTS, click OK, and what I've now done is I've managed to very quickly select every third cell.
You say, well, what good is that?
How do you nudge the selection up, which is a great question, but here’s what we’re going to do.
We’re going to switch over to VBA.
ALT+F11, ALT+F11, CONTROL+G, and this beautiful little bit of code here.
From the selection, we're going to offset some number of rows down, some number of columns over, and select, alright?
So, looking at the data, I want to go up to Bill Jelen in E23.
Actually, I want to go to Kuldeep in E24, which is 2 rows up.
So, I'm going to say go up 2 rows, press ENTER, and that selection, which was every third cell, will now move up.
Isn't that just awesome, and, in fact, I wrote four little macros, one called move up, move down, move left, move right, because when I showed Kuldeep this method, he’s like, oh no, there's 20 things I have to do.
So, here, we'll click the move up macro which should move me up to row 23, and then the move right macro, the move right macro, move down, left, left, right?
Look, we could just…we can…once we get that thing in there, we are really cool to be able to move things around, you know, and then, from there, there was a whole bunch of other things Kuldeep wanted to do.
He wanted to copy and paste special transpose somewhere else, right, and that's all easy stuff, relatively.
It’s getting every third cell selected.
Now, what I'm going to show you here -- because I'm suspecting Kuldeep wants to do this all in a macro and doesn't want to have to do the steps again and again and again, and hey I applaud that, that's awesome -- is I’m going to say to Kuldeep, the first thing I want to do is I want you to select all of the cells.
Where you want to select every third cell, I want you to select all the cells like that, alright, and then we'll say count how many columns we have, figure out where we started, and then figure out the next available row, and I'm using the USEDRANGE to figure out where the last row is.
I'm adding 2 rows to that, and so what that should do is just find a nice blank row down in row 27.
Let’s go take a look, and so 27 is a blank row.
The code is going to put a 1 in column 5 of that blank row, and then it's going to copy 3 cells.
So, E, F, and G are in the 3 cells that we're going to copy there and we're going to copy it to the 1500 columns, which will generate a series of 1s and blank, blank, 1, blank, blank, 1, blank, blank, all the way out to the end of our data, and then the SPECIALCELLS, this is home, find and select, go to special cells, choose the constants, .SELECT.
Alright, so, now, it's selected that data, and then we know that we want to go 4 rows up and, bam, like that, it goes up, in this case to row 23.
Kuldeep wants row 24, adjust the code one bit and then press the down arrow, and we're good to go.
That's the best way I have to do it.
If anyone has a better method to select every third cell with just some magic cool way to do that or every other cell or every fourth cell, by all means, leave some comments down in the YouTube comments.
Well, hey.
I haven't promoted the Power Excel With MrExcel book or the VBA with macros book, either one.
Lots of great excel tips and tricks in those books.
Click that i in the top right-hand corner to read more about the book.
Alright.
So, in this episode, Kuldeep wants to the select every third cell for 1500 cells.
He tried to write a macro to union all 500 cells, but it's too long and causes a compile error.
So, solution 1: build a 3-cell array with 1, blank, blank; copy those 3 cells and paste to 1500 cells; that creates a range of 1500 cell column wide range with 1, blank, blank, 1, blank blank; select the 1500 cells, find and select, go to special, constants, selects every third cell; then that one line of VBA, selection.offset.select, to move up some number of rows or columns or anything; and then finally to add a VBA Macro to do the whole thing.
I want to thank Kuldeep for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
This is a doozy. Kuldeep sends in a question.
He says he has data in E24 to BEV24. That's 1500 cells.
He needs to select every third cell.
A note, the union macro is too long and generates errors.
What's he talking about, the union macro?
He's talking about, he's already tried this, HOWTOSELECTEVERYTHIRDCELL.
Record a little macro here.
Select this cell and then CONTROL keys, that cell, that cell, that cell, that cell, that cell.
I didn't go too far. I just want to see how they're recording the code.
Stop recording. ALT+F8.
HOWTOSELECTEVERYTHIRDCELL, EDIT, and this is really bad.
There's no magic way to select every third cell.
You just have to list all 500 cells in there, which would take longer than selecting the 500 cells, right?
Just pointless.
Pointless, pointless, pointless.
So that the macro recorder has no better way to do this.
So I said to Kuldeep, what we're going to have to do, the thing we have to leverage, is special cells.
I said, by any chance, is every third cell numeric and everything else is text?
No, he says.
Alright, so here's what I'm going to do.
I'm going to go to a brand new row and put a 1 and then nothing, nothing, and I'm going to copy these three cells, those three cells like that, and then what I’m going to do is I’m going to go to the end and select all of those cells.
Now, this is 1500 cells which is a multiple of 3, and paste, and what that's going to do for me is it's going to give me every third cell is going to be numeric, like that, and the beautiful thing is, what we can do then is, select from here, type BEV26, and I'll hold down the SHIFT key when I click OK, and that will select to the end, and then HOME, FIND & SELECT, GO TO SPECIAL, and I want the CONSTANTS, click OK, and what I've now done is I've managed to very quickly select every third cell.
You say, well, what good is that?
How do you nudge the selection up, which is a great question, but here’s what we’re going to do.
We’re going to switch over to VBA.
ALT+F11, ALT+F11, CONTROL+G, and this beautiful little bit of code here.
From the selection, we're going to offset some number of rows down, some number of columns over, and select, alright?
So, looking at the data, I want to go up to Bill Jelen in E23.
Actually, I want to go to Kuldeep in E24, which is 2 rows up.
So, I'm going to say go up 2 rows, press ENTER, and that selection, which was every third cell, will now move up.
Isn't that just awesome, and, in fact, I wrote four little macros, one called move up, move down, move left, move right, because when I showed Kuldeep this method, he’s like, oh no, there's 20 things I have to do.
So, here, we'll click the move up macro which should move me up to row 23, and then the move right macro, the move right macro, move down, left, left, right?
Look, we could just…we can…once we get that thing in there, we are really cool to be able to move things around, you know, and then, from there, there was a whole bunch of other things Kuldeep wanted to do.
He wanted to copy and paste special transpose somewhere else, right, and that's all easy stuff, relatively.
It’s getting every third cell selected.
Now, what I'm going to show you here -- because I'm suspecting Kuldeep wants to do this all in a macro and doesn't want to have to do the steps again and again and again, and hey I applaud that, that's awesome -- is I’m going to say to Kuldeep, the first thing I want to do is I want you to select all of the cells.
Where you want to select every third cell, I want you to select all the cells like that, alright, and then we'll say count how many columns we have, figure out where we started, and then figure out the next available row, and I'm using the USEDRANGE to figure out where the last row is.
I'm adding 2 rows to that, and so what that should do is just find a nice blank row down in row 27.
Let’s go take a look, and so 27 is a blank row.
The code is going to put a 1 in column 5 of that blank row, and then it's going to copy 3 cells.
So, E, F, and G are in the 3 cells that we're going to copy there and we're going to copy it to the 1500 columns, which will generate a series of 1s and blank, blank, 1, blank, blank, 1, blank, blank, all the way out to the end of our data, and then the SPECIALCELLS, this is home, find and select, go to special cells, choose the constants, .SELECT.
Alright, so, now, it's selected that data, and then we know that we want to go 4 rows up and, bam, like that, it goes up, in this case to row 23.
Kuldeep wants row 24, adjust the code one bit and then press the down arrow, and we're good to go.
That's the best way I have to do it.
If anyone has a better method to select every third cell with just some magic cool way to do that or every other cell or every fourth cell, by all means, leave some comments down in the YouTube comments.
Well, hey.
I haven't promoted the Power Excel With MrExcel book or the VBA with macros book, either one.
Lots of great excel tips and tricks in those books.
Click that i in the top right-hand corner to read more about the book.
Alright.
So, in this episode, Kuldeep wants to the select every third cell for 1500 cells.
He tried to write a macro to union all 500 cells, but it's too long and causes a compile error.
So, solution 1: build a 3-cell array with 1, blank, blank; copy those 3 cells and paste to 1500 cells; that creates a range of 1500 cell column wide range with 1, blank, blank, 1, blank blank; select the 1500 cells, find and select, go to special, constants, selects every third cell; then that one line of VBA, selection.offset.select, to move up some number of rows or columns or anything; and then finally to add a VBA Macro to do the whole thing.
I want to thank Kuldeep for sending that question in and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
