MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Select Every Other Cell


July 06, 2017 - by Bill Jelen

Select Every Other Cell

Kuldeep needs to select every third cell through a range of 1500 cells. MrExcel provides a crazy solution.

Watch Video

  • 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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2108 select every third cell
  • this is a doozy cool deep sends into
  • question says he has data e24 to be evey
  • 24 that's 1500 cells you need to select
  • every third cell Branson 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 how to select every third cell
  • little record a little macro here select
  • a cell and then control keys that cell
  • that cell that cell that cell that's all
  • I need to go too far I just want to see
  • how they're recording the code stop
  • recording alt f8 how to select every
  • third cell edit and this is really bad
  • there's no magic way to select every
  • third so 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 kill deep
  • what we're going to have to do the thing
  • we have to leverage is special cells I
  • said by chance is every third cell
  • numeric and everything else has text no
  • he says all right so here's what I'm
  • gonna do I'm gonna 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
  • we're going to do is I'm gonna go to the
  • end and select all of those cells
  • 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 took the
  • Eevee 26 I'll hold down the shift key
  • when I click OK and that will select to
  • the end and then home find and select go
  • to special and I want the constants
  • click OK and what I've now done is I've
  • managed up very quickly select every
  • third cell and say well what good is
  • that how do you nudge the selection up
  • which is a great question
  • but here
  • numerous switch over to VBA alt f11 alt
  • F 11 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
  • e20 you actually want to go to Col deep
  • in e 24 which is two rows up so I'm
  • going to say go up two 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 cultiva this
  • methi's like oh no there's 20 things
  • after 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 can just we can once we get that
  • that thing in there we are really cool
  • to be able to move things around you
  • know and then from there there was a
  • lunch of other things cool teeth wanted
  • to do he wanted to copy and paste us to
  • 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 cause he wants to do this
  • all in a macro and doesn't have to do
  • the steps again and again and again and
  • hey I applaud that that's awesome is
  • i'ma say the culty the first thing I
  • want to do is I want you to select all
  • the cells where you want to select every
  • third cell once you select all the cells
  • like that right 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
  • used range to figure out where the last
  • row is I'm adding two 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 EF and G are the 3 cells
  • we're going to copy there and we're
  • going to copy it to the 1,500 columns
  • which will generate a series of ones and
  • blank blank one blank blank one blank
  • blank all the way out to the end of our
  • data
  • and then the special sells is home
  • finance elect go to special sells choose
  • the constants dot select all right so
  • now it's selected that data and then we
  • know that we want to go four rows up and
  • bam like that it goes up in this case
  • Road 23 cool D once Road 24 just 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 watch this probably
  • something magic cool way to do that or
  • every other cell every fourth cell by
  • all means leave some comments down in
  • the YouTube comments well I haven't
  • promoted the power cell with MrExcel
  • book or the VBA with macros book either
  • one lots of great excel tips and tricks
  • in those books click that I on the top
  • right hand corner to read more about the
  • book alright so in this episode Kolby +
  • the select every third cell for 1500
  • cells he tried to write a macro to Union
  • all 5m cells but it's too long and
  • causes a compiler so solution 1 build a
  • 3 cell array with one blank blank copy
  • those 3 cells and paste to 1500 cells
  • that creates a range of 1500 cell column
  • wide range with one blank blank one
  • blank blank selected 59 our cells find
  • and select go to special constants
  • selects every third cell and that one
  • line of VBA selection dot offset dot
  • select to move up some number of rows or
  • columns or anything and finally to have
  • a VBA macro to do the whole thing one of
  • the cool deep for sending that question
  • in I'm with ank you for stopping but
  • I'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2108.xlsm

Title Photo: ulleo / Pixabay


Bill Jelen is the author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.