0

I would like to start at cell A2 and then select every 6th cell in that column. Next, I want to insert a cell and shift everything to the right.

This is what I have that works so far, but it's long and takes forever to type out: 

Range("A2,A8,A14,A20,A26,A32,A38,A44,A50,A56,A62,A68,A74").Select
Selection.Insert Shift:=xlToRight

Please tell me there is a more simple expression to get the desired output.

Thanks.

vacip
  • 5,246
  • 2
  • 26
  • 54
Krista
  • 261
  • 1
  • 5

3 Answers3

1

do it in a loop so you don't have to type it all out. 

for i = 2 to 10000 step 6
  range("A" & i).Select 
  Selection.Insert Shift:=xlToRight

next

you don't have to do the select, you can also do range("A"&i).insert Shift:=xlToRight

chungtinhlakho
  • 870
  • 10
  • 21
  • 1
    I agree with [not using select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Also, use the `Cells(i,1)` command instead of concatenating range names. [It is faster and cleaner](http://stackoverflow.com/questions/36073943/range-vs-cells-run-times/). – vacip Jun 04 '16 at 00:11
0

You can use Union Function to set the range and then move all cells together.

Sub Demo()
    Dim rng As Range
    Dim lastRow As Long

    'get last row in column "A"
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row 

    'set range using Union function
    For i = 2 To lastRow Step 6
        If rng Is Nothing Then Set rng = Range("A" & i)
        Set rng = Union(Range("A" & i), rng)
    Next

    'move range to right
    rng.Insert Shift:=xlToRight
End Sub
Mrig
  • 11,612
  • 2
  • 13
  • 27
0

you could go like follows:

For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants, xlNumbers)
    If (cell.Row - 2) Mod 6 = 0 Then cell.Insert Shift:=xlToRight
Next cell

but should your need be not actually shifting cells (i.e. shifting ALL row cells at the right of the relevant one) but rather their values only then you could go like follows:

    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
        If (cell.Row - 2) Mod 6 = 0 Then cell.Offset(, 1) = cell: cell.ClearContents
    Next cell

which is much faster

a few words about what you see in code above:

  • the Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) part takes care to consider only column "A" cells from "A1" down to its LAST non empty one

  • the .SpecialCells(xlCellTypeConstants) part takes care to furtherly filter the former range considering only cells with a "constant" (i.e. not resulting from a formula) value in it

    you could even be more specific like

    • SpecialCells(xlCellTypeConstants, xlTextValues)

      which would consider only "constants" (as above defined) string values

    • SpecialCells(xlCellTypeConstants, xlNumbers)

      which would consider only "constants" (as above defined) numeric values

    the same can be achieved for cell with formulas only just by typing xlCellTypeFormulas instead of xlCellTypeConstants

user3598756
  • 28,893
  • 4
  • 18
  • 28