For next loops

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

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


--
Don Guillett
SalesAid Software
[email protected]
"Kate" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Fantastic! I used:

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

Thanks alot!

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?

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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Kate" <[email protected]> wrote in message
news:[email protected]...
> Fantastic! I used:
>
> For i = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
> Rows(i).Insert
> Rows(i).Insert
>
> Thanks alot!
>

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


--
Don Guillett
SalesAid Software
[email protected]
"Kate" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>