Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Chart the Maximum Point within Two Loops | Charts and Charting in Excel | |||
Edit this macro code to loop and end | Excel Discussion (Misc queries) | |||
Do loops | Excel Discussion (Misc queries) | |||
Max limit of 7 nested loops | Excel Worksheet Functions |