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

For next loops
 

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

"Kate" wrote in message
ups.com...
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

For next loops
 

Fantastic! I used:

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

Thanks alot!

For next loops
 

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?

For next loops
 

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" wrote in message
ups.com...
Fantastic! I used:

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

Thanks alot!

For next loops
 

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

"Kate" wrote in message
oups.com...

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?