how do i write a macro that will add rows into a spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

im trying to add 15 rows in between each row in a spreadsheet
all of which contain information i would like to use as headers
i have approximately 1600 rows in this worksheet
im assuming a macro may be the easiest way to do this but am open to any
suggestions that any of you may have
thank you for your assistance
brock
 
Hi BROCK:

This should work if you paste it into a module.

If you need any further help, please let me know.

Thanks,

Simon

Sub Row_Adder()
Dim curr_row, no_insert, start_row, exec_loop, offset, ins_stop, i As Integer

' Set the row you want the code to start running from
start_row = 20

'Set the number of rows you want to insert
no_insert = 15

'Set the number of times you want this code to run
exec_loop = 20

'Set the number of rows you want to offset each time. eg: if you
'want to insert 10 rows then move down 5 rows before inserting
'the next ten
offset = 2


'Move to the start

Rows(start_row).Select


For i = 1 To exec_loop

curr_row = ActiveCell.Row
ins_stop = curr_row + no_insert

'Select and insert the rows

Rows(curr_row & ":" & ins_stop).Select
Selection.Insert Shift:=xlDown

'select the bottom of your insert
Rows(ins_stop).Select

'move to the offset
Rows(ActiveCell.Row + offset).Select

Next i


End Sub

im trying to add 15 rows in between each row in a spreadsheet
all of which contain information i would like to use as headers
i have approximately 1600 rows in this worksheet
im assuming a macro may be the easiest way to do this but am open to any
suggestions that any of you may have
thank you for your assistance
brock
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
 
thank you for your help smw226
the language you provided worked perfectly
so perfectly in fact that i was wondering if i might push my luck
my workbook contains two worksheets
worksheet 1 has 1600 rows of information
worksheet 2 has basically the same info but each row that contains that info
is now seperated by 15 lines thanx to you
is there a way to insert hyperlinks into column A of worksheet 1 that will
link each row to its corresponding row in worksheet 2?
any advice on this matter will be greatly appreciated and thank you for the
time youve already spent on me
brock

smw226 via OfficeKB.com said:
Hi BROCK:

This should work if you paste it into a module.

If you need any further help, please let me know.

Thanks,

Simon

Sub Row_Adder()
Dim curr_row, no_insert, start_row, exec_loop, offset, ins_stop, i As Integer

' Set the row you want the code to start running from
start_row = 20

'Set the number of rows you want to insert
no_insert = 15

'Set the number of times you want this code to run
exec_loop = 20

'Set the number of rows you want to offset each time. eg: if you
'want to insert 10 rows then move down 5 rows before inserting
'the next ten
offset = 2


'Move to the start

Rows(start_row).Select


For i = 1 To exec_loop

curr_row = ActiveCell.Row
ins_stop = curr_row + no_insert

'Select and insert the rows

Rows(curr_row & ":" & ins_stop).Select
Selection.Insert Shift:=xlDown

'select the bottom of your insert
Rows(ins_stop).Select

'move to the offset
Rows(ActiveCell.Row + offset).Select

Next i


End Sub

im trying to add 15 rows in between each row in a spreadsheet
all of which contain information i would like to use as headers
i have approximately 1600 rows in this worksheet
im assuming a macro may be the easiest way to do this but am open to any
suggestions that any of you may have
thank you for your assistance
brock
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
Click to expand...
 
Hi Brock,

Just to confirm my uderstanding:

Do you want, for example, the first hyperlink in sheet!A1 to reference Sheet2!
A1, then Sheet!A2 to reference Sheet2!A16 etc ?

Thanks,

Simon


thank you for your help smw226
the language you provided worked perfectly
so perfectly in fact that i was wondering if i might push my luck
my workbook contains two worksheets
worksheet 1 has 1600 rows of information
worksheet 2 has basically the same info but each row that contains that info
is now seperated by 15 lines thanx to you
is there a way to insert hyperlinks into column A of worksheet 1 that will
link each row to its corresponding row in worksheet 2?
any advice on this matter will be greatly appreciated and thank you for the
time youve already spent on me
brock
Hi BROCK:
Click to expand...
[quoted text clipped - 55 lines]
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
 
yes simon,
thats exactly what im trying to do
brock

smw226 via OfficeKB.com said:
Hi Brock,

Just to confirm my uderstanding:

Do you want, for example, the first hyperlink in sheet!A1 to reference Sheet2!
A1, then Sheet!A2 to reference Sheet2!A16 etc ?

Thanks,

Simon


thank you for your help smw226
the language you provided worked perfectly
so perfectly in fact that i was wondering if i might push my luck
my workbook contains two worksheets
worksheet 1 has 1600 rows of information
worksheet 2 has basically the same info but each row that contains that info
is now seperated by 15 lines thanx to you
is there a way to insert hyperlinks into column A of worksheet 1 that will
link each row to its corresponding row in worksheet 2?
any advice on this matter will be greatly appreciated and thank you for the
time youve already spent on me
brock
Hi BROCK:
Click to expand...
[quoted text clipped - 55 lines]
thank you for your assistance
brock
Click to expand...
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
Click to expand...
 
thank you again for the time and attention youre giving me
youre understanding is 100% accurate
that is exactly what im trying to do
brock

smw226 via OfficeKB.com said:
Hi Brock,

Just to confirm my uderstanding:

Do you want, for example, the first hyperlink in sheet!A1 to reference Sheet2!
A1, then Sheet!A2 to reference Sheet2!A16 etc ?

Thanks,

Simon


thank you for your help smw226
the language you provided worked perfectly
so perfectly in fact that i was wondering if i might push my luck
my workbook contains two worksheets
worksheet 1 has 1600 rows of information
worksheet 2 has basically the same info but each row that contains that info
is now seperated by 15 lines thanx to you
is there a way to insert hyperlinks into column A of worksheet 1 that will
link each row to its corresponding row in worksheet 2?
any advice on this matter will be greatly appreciated and thank you for the
time youve already spent on me
brock
Hi BROCK:
Click to expand...
[quoted text clipped - 55 lines]
thank you for your assistance
brock
Click to expand...
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200704/1
Click to expand...
 
Back
Top