For next loops

[Kate]

Hi,
I want to format an excel sheet using a macro. This involves inserting
new rows etc, but the number of rows to insert depends on the number of
entries that are in the original form. Each entry is given a
consecutive number. Currently, the code is as follows:

Range("3:3,5:5,7:7,9:9,11:11,13:13,15:15").Select
Range("A15").Activate
Selection.Insert Shift:=xlDown

Instead of using numbers (since these will change), I want to do
something like:

for j=3 to 3+2*(max(B2:B200)-2) step 2
Range("j:j").Select
Selection.Insert Shift:=xlDown
next j
end

This doesn't work. It doesn't like Range("j:j").Select. I have tried
declaring j as various things but still no luck. I'm sure this must be
something very simple. Can anyone help?

Thanks

 

[Don Guillett]

if below is what you want, try
Sub insertrows()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -2
Rows(i).Insert
Next
End Sub


1
2

3
4

5
6

7
8

9
10

 

[Kate]

Fantastic! I used:

For i = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
Rows(i).Insert
Rows(i).Insert

Thanks alot!

 

[Kate]

Using that theory, how do you select actual cells?

For i = Cells(Rows.Count, "a").End(xlUp).Row To 12 Step -3
Range("Ci:Oi").Select
with selection
..mergecells = true
end with
next

clearly you can't go Ci:Oi, so what would you use?

 

[Dana DeLouis]

If the # of rows to insert were a variable, another option might be:

Dim R As Long
For R = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
Rows(R).Resize(2).Insert
Next R

 

[Don Guillett]

not quite sure what you want here but

For i = Cells(Rows.Count, "a").End(xlUp).Row To 12 Step -3
range(cells(i,"c"),cells(i,"o")).mergecells=true
next