.insert Shift:=xlDown 1

[PWD]

Hi peeps. I have pinched most of the following code from a previous post. All I want to be able to do is move 3 columns down at once.

Sub Budgets()
    With Range([j6], [j6].End(xlDown))
        Row = .Row
        r1 = .Row + .Rows.count - 1
        Do While Row <= r1
          With Cells(Row, "j")
            If (.Value) <> Cells(Row, "p") Then
              Cells(Row, "p").insert Shift:=xlDown
              Cells(Row, "q").insert Shift:=xlDown
              Cells(Row, "r").insert Shift:=xlDown
              Row = Row + 1
            End If
            Row = Row + 1
          End With
        Loop
    End With
End Sub

I've tried the following combinations - that don't work!!

Cells((Row, "p"):(Row, "r")).insert shift:=xlDown
Cells((Row, "p"):Cells(Row, "r")).insert shift:=xlDown
Range((Row, "p"):(Row, "r")).insert shift:=xlDown
Cells(Row, "p":"r").insert shift:=xlDown
Cells("P,Row:R,Row").insert Shift:=xlDown
Range("P,Row:R,Row").insert Shift:=xlDown

Is it possible to do what I want in one line? If so I'm sure it will be so obvious.....

Many thanks,
Des.

 

[Fenrirshowl]

Try something like:

Rows(ActiveCell.Row & ":" & ActiveCell.Row + 2).Insert

Any good?

 

[PWD]

'fraid not. There's no "ActiveCell" during the macro process so it just picks on wherever the last cell that was selected.

Des.

 

[kjv1611]

PWD,

The last cell selected cell WOULD be the 'ActiveCell', would it not?


--

"If to err is human, then I must be some kind of human!" -Me

 

[Fenrirshowl]

I did say LIKE as I was being lazy not incorporating it into your code. How about

Rows(Row & ":" & Row + 2).Insert

?

 

[PWD]

Hi guys. I read the 'Tips' and didn't include more than I thought was necessary to get a solution but now I feel the need. Basically I've got 2 sets of columns of 3 cells. Each set of columns has a category and then a budget and an actual figure - making up 3 cells in that row. I know that the first set of 3 columns has ALL the categories. The second set of 3 columns has fewer categories but they occur in the same order as in the first. What I have to do is insert blanks into the 3 columns of the second set where there's a mis-match so that the categories all line up at the end. For example, if the category in column J row 10 doesn't match that in column P row 10 I have to move P10, Q10 & R10 down one row.

OTHER / TEMP STAFF 789 84 OTHER / TEMP STAFF 0 67
ELECTRICITY 3561 2860 WATER 75 0
etc.

becomes

OTHER / TEMP STAFF 789 84 OTHER / TEMP STAFF 0 67
ELECTRICITY 3561 2860
WATER 567 642 WATER 75 0

It works fine with the 3 lines of code that I've put in (after I removed one of the "Row = Row + 1")!!; I was just curious as to whether it could be condensed to 1 line.

Des.

 

[Fenrirshowl]

How about:

Sub insertcells()
x = 6 ' x is the first row
While Cells(x, 2) <> ""
    While Cells(x, 16) <> Cells(x, 10) ' 10 denotes col J, 16 col P
        Range(Cells(x, 16), Cells(x, 18)).Insert shift:=xlDown
        x = x + 1
    Wend
    x = x + 1
Wend
End Sub

?

 

[PWD]

How about?, indeed!! It works a treat.

I don't think I would have thought of the combination of

Range(Cells(x, 16), Cells(x, 18))

The nested 'Whiles' seem pretty neat too!!

Many thanks,
Des.

 

[Fenrirshowl]

No problem.

(I have to admit I didn't use the Range/Cells combo until I saw them used in a thread on TT.)

 

[PWD]

I get most of my best ideas from here - then cram them all into my Personal.xls for future use.

Des.