"Insert" Macro

I am looking for a way to insert rows underneath a row, with a macro reading
a cell, and then inserting that number of rows underneath.

e.g. A column reads 500-505. The macro will seperate the numbers (500,505)
into adjecent cells, calculate the difference (5), and then insert that
number of columns underneath the original row (reading "500-505" with 5 blank
rows underneath). I figured out how to do the calculation, but I can't find
a way to insert the columns. Is there a way to do this? I already know the
answer is "yes", but is it feasable for a beginner macro'er to try and
implement?

Thanks,
Sean H

try
Sub insertcalculatedrows()
'broken down so you can see what happens
On Error Resume Next
For Each c In Selection
x = InStr(c, "-")
y = Right(c, Len(c) - x)
z = Left(c, Len(c) - x)
c.Offset(1).Resize(y - z, 1).EntireRow.Insert
Next
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Sean H" <[email protected]> wrote in message
news:[email protected]...
> I am looking for a way to insert rows underneath a row, with a macro
reading
> a cell, and then inserting that number of rows underneath.
>
> e.g. A column reads 500-505. The macro will seperate the numbers
(500,505)
> into adjecent cells, calculate the difference (5), and then insert that
> number of columns underneath the original row (reading "500-505" with 5
blank
> rows underneath). I figured out how to do the calculation, but I can't
find
> a way to insert the columns. Is there a way to do this? I already know
the
> answer is "yes", but is it feasable for a beginner macro'er to try and
> implement?
>
> Thanks,
> Sean H

Sean

try one of these:

Sub InsertRows()
' all variables defined
' step by step approach
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long
On Error GoTo NoHyphen
CellContent = ActiveCell.Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Sub InsertRowsShortVersion()
' no defined variables
On Error GoTo NoHyphen
ActiveCell.Offset(1, 0).Resize( _
--Right( _
ActiveCell.Value, _
Len(ActiveCell.Value) - _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value)) - _
--Left( _
ActiveCell.Value, _
WorksheetFunction.Find( _
"-", _
ActiveCell.Value) - 1) _
).EntireRow.Insert
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

Regards

Trevor


"Sean H" <[email protected]> wrote in message
news:[email protected]...
>I am looking for a way to insert rows underneath a row, with a macro
>reading
> a cell, and then inserting that number of rows underneath.
>
> e.g. A column reads 500-505. The macro will seperate the numbers
> (500,505)
> into adjecent cells, calculate the difference (5), and then insert that
> number of columns underneath the original row (reading "500-505" with 5
> blank
> rows underneath). I figured out how to do the calculation, but I can't
> find
> a way to insert the columns. Is there a way to do this? I already know
> the
> answer is "yes", but is it feasable for a beginner macro'er to try and
> implement?
>
> Thanks,
> Sean H

Trevor,

Thanks a lot, that worked great. Here is another problem of mine. Is there
a way to loop this so that I can execute the macro once, and have it expand
my whole file? After that, the macro needs to fill in the numbers between
(and including) the numbers. My file looks something like this:

505-520
521-530
532-540

As you will notcie, sometimes the range jumps a number and leaves it out,
eliminating my option for a simple x = x + 1 style loop.

Thanks again for all your help.

Regards,
Sean Heckathorne

"Trevor Shuttleworth" wrote:

> Sean
>
> try one of these:
>
> Sub InsertRows()
> ' all variables defined
> ' step by step approach
> Dim CellContent As String
> Dim FirstNumber As Long
> Dim SecondNumber As Long
> Dim Difference As Long
> Dim BreakPoint As Long
> On Error GoTo NoHyphen
> CellContent = ActiveCell.Value
> BreakPoint = WorksheetFunction.Find("-", CellContent)
> FirstNumber = --Left(CellContent, BreakPoint - 1)
> SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
> Difference = SecondNumber - FirstNumber
> ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
> Exit Sub
> NoHyphen:
> MsgBox "Invalid Format", vbCritical, "No Hyphen"
> End Sub
>
> Sub InsertRowsShortVersion()
> ' no defined variables
> On Error GoTo NoHyphen
> ActiveCell.Offset(1, 0).Resize( _
> --Right( _
> ActiveCell.Value, _
> Len(ActiveCell.Value) - _
> WorksheetFunction.Find( _
> "-", _
> ActiveCell.Value)) - _
> --Left( _
> ActiveCell.Value, _
> WorksheetFunction.Find( _
> "-", _
> ActiveCell.Value) - 1) _
> ).EntireRow.Insert
> Exit Sub
> NoHyphen:
> MsgBox "Invalid Format", vbCritical, "No Hyphen"
> End Sub
>
> Regards
>
> Trevor
>
>
> "Sean H" <[email protected]> wrote in message
> news:[email protected]...
> >I am looking for a way to insert rows underneath a row, with a macro
> >reading
> > a cell, and then inserting that number of rows underneath.
> >
> > e.g. A column reads 500-505. The macro will seperate the numbers
> > (500,505)
> > into adjecent cells, calculate the difference (5), and then insert that
> > number of columns underneath the original row (reading "500-505" with 5
> > blank
> > rows underneath). I figured out how to do the calculation, but I can't
> > find
> > a way to insert the columns. Is there a way to do this? I already know
> > the
> > answer is "yes", but is it feasable for a beginner macro'er to try and
> > implement?
> >
> > Thanks,
> > Sean H
>
>
>

Also,

Is there a way to save this macro so that I can use it every time I open a
new workbook, until I decide to delete the macro?

Thanks,
Sean Heckathorne

"Trevor Shuttleworth" wrote:

> Sean
>
> try one of these:
>
> Sub InsertRows()
> ' all variables defined
> ' step by step approach
> Dim CellContent As String
> Dim FirstNumber As Long
> Dim SecondNumber As Long
> Dim Difference As Long
> Dim BreakPoint As Long
> On Error GoTo NoHyphen
> CellContent = ActiveCell.Value
> BreakPoint = WorksheetFunction.Find("-", CellContent)
> FirstNumber = --Left(CellContent, BreakPoint - 1)
> SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
> Difference = SecondNumber - FirstNumber
> ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
> Exit Sub
> NoHyphen:
> MsgBox "Invalid Format", vbCritical, "No Hyphen"
> End Sub
>
> Sub InsertRowsShortVersion()
> ' no defined variables
> On Error GoTo NoHyphen
> ActiveCell.Offset(1, 0).Resize( _
> --Right( _
> ActiveCell.Value, _
> Len(ActiveCell.Value) - _
> WorksheetFunction.Find( _
> "-", _
> ActiveCell.Value)) - _
> --Left( _
> ActiveCell.Value, _
> WorksheetFunction.Find( _
> "-", _
> ActiveCell.Value) - 1) _
> ).EntireRow.Insert
> Exit Sub
> NoHyphen:
> MsgBox "Invalid Format", vbCritical, "No Hyphen"
> End Sub
>
> Regards
>
> Trevor
>
>
> "Sean H" <[email protected]> wrote in message
> news:[email protected]...
> >I am looking for a way to insert rows underneath a row, with a macro
> >reading
> > a cell, and then inserting that number of rows underneath.
> >
> > e.g. A column reads 500-505. The macro will seperate the numbers
> > (500,505)
> > into adjecent cells, calculate the difference (5), and then insert that
> > number of columns underneath the original row (reading "500-505" with 5
> > blank
> > rows underneath). I figured out how to do the calculation, but I can't
> > find
> > a way to insert the columns. Is there a way to do this? I already know
> > the
> > answer is "yes", but is it feasable for a beginner macro'er to try and
> > implement?
> >
> > Thanks,
> > Sean H
>
>
>

You could put it in a workbook and store that workbook in your XLStart folder.

Many people who want to have this kind of macro available each time they open
excel will name that file personal.xls.



Sean H wrote:
>
> Also,
>
> Is there a way to save this macro so that I can use it every time I open a
> new workbook, until I decide to delete the macro?
>
> Thanks,
> Sean Heckathorne
>
> "Trevor Shuttleworth" wrote:
>
> > Sean
> >
> > try one of these:
> >
> > Sub InsertRows()
> > ' all variables defined
> > ' step by step approach
> > Dim CellContent As String
> > Dim FirstNumber As Long
> > Dim SecondNumber As Long
> > Dim Difference As Long
> > Dim BreakPoint As Long
> > On Error GoTo NoHyphen
> > CellContent = ActiveCell.Value
> > BreakPoint = WorksheetFunction.Find("-", CellContent)
> > FirstNumber = --Left(CellContent, BreakPoint - 1)
> > SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
> > Difference = SecondNumber - FirstNumber
> > ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
> > Exit Sub
> > NoHyphen:
> > MsgBox "Invalid Format", vbCritical, "No Hyphen"
> > End Sub
> >
> > Sub InsertRowsShortVersion()
> > ' no defined variables
> > On Error GoTo NoHyphen
> > ActiveCell.Offset(1, 0).Resize( _
> > --Right( _
> > ActiveCell.Value, _
> > Len(ActiveCell.Value) - _
> > WorksheetFunction.Find( _
> > "-", _
> > ActiveCell.Value)) - _
> > --Left( _
> > ActiveCell.Value, _
> > WorksheetFunction.Find( _
> > "-", _
> > ActiveCell.Value) - 1) _
> > ).EntireRow.Insert
> > Exit Sub
> > NoHyphen:
> > MsgBox "Invalid Format", vbCritical, "No Hyphen"
> > End Sub
> >
> > Regards
> >
> > Trevor
> >
> >
> > "Sean H" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am looking for a way to insert rows underneath a row, with a macro
> > >reading
> > > a cell, and then inserting that number of rows underneath.
> > >
> > > e.g. A column reads 500-505. The macro will seperate the numbers
> > > (500,505)
> > > into adjecent cells, calculate the difference (5), and then insert that
> > > number of columns underneath the original row (reading "500-505" with 5
> > > blank
> > > rows underneath). I figured out how to do the calculation, but I can't
> > > find
> > > a way to insert the columns. Is there a way to do this? I already know
> > > the
> > > answer is "yes", but is it feasable for a beginner macro'er to try and
> > > implement?
> > >
> > > Thanks,
> > > Sean H
> >
> >
> >

--

Dave Peterson

Sean

this will loop through a selection and create the blank lines.

Sub InsertRowsLoop()
' all variables defined
' step by step approach
Dim SelectedColumn As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCount As Long
Dim CellContent As String
Dim FirstNumber As Long
Dim SecondNumber As Long
Dim Difference As Long
Dim BreakPoint As Long

SelectedColumn = Selection.Column
FirstRow = Selection.Row
LastRow = Selection.Row + Selection.Rows.Count - 1

On Error GoTo NoHyphen
' Note: work up from the bottom
For iCount = LastRow To FirstRow Step -1
CellContent = Cells(iCount, SelectedColumn).Value
BreakPoint = WorksheetFunction.Find("-", CellContent)
FirstNumber = --Left(CellContent, BreakPoint - 1)
SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
Difference = SecondNumber - FirstNumber
Cells(iCount, SelectedColumn).Offset(1,
0).Resize(Difference).EntireRow.Insert
Next 'iCount
Exit Sub
NoHyphen:
MsgBox "Invalid Format", vbCritical, "No Hyphen"
End Sub

I'll leave you to increment the numbers. All you need to do is work down
from the top and, if the cell is blank, set it to the previous cell plus 1.
No doubt there are other ways using AutoFill, etc.

Regards

Trevor


"Sean H" <[email protected]> wrote in message
news:[email protected]...
> Trevor,
>
> Thanks a lot, that worked great. Here is another problem of mine. Is
> there
> a way to loop this so that I can execute the macro once, and have it
> expand
> my whole file? After that, the macro needs to fill in the numbers between
> (and including) the numbers. My file looks something like this:
>
> 505-520
> 521-530
> 532-540
>
> As you will notcie, sometimes the range jumps a number and leaves it out,
> eliminating my option for a simple x = x + 1 style loop.
>
> Thanks again for all your help.
>
> Regards,
> Sean Heckathorne
>
> "Trevor Shuttleworth" wrote:
>
>> Sean
>>
>> try one of these:
>>
>> Sub InsertRows()
>> ' all variables defined
>> ' step by step approach
>> Dim CellContent As String
>> Dim FirstNumber As Long
>> Dim SecondNumber As Long
>> Dim Difference As Long
>> Dim BreakPoint As Long
>> On Error GoTo NoHyphen
>> CellContent = ActiveCell.Value
>> BreakPoint = WorksheetFunction.Find("-", CellContent)
>> FirstNumber = --Left(CellContent, BreakPoint - 1)
>> SecondNumber = --Right(CellContent, Len(CellContent) - BreakPoint)
>> Difference = SecondNumber - FirstNumber
>> ActiveCell.Offset(1, 0).Resize(Difference).EntireRow.Insert
>> Exit Sub
>> NoHyphen:
>> MsgBox "Invalid Format", vbCritical, "No Hyphen"
>> End Sub
>>
>> Sub InsertRowsShortVersion()
>> ' no defined variables
>> On Error GoTo NoHyphen
>> ActiveCell.Offset(1, 0).Resize( _
>> --Right( _
>> ActiveCell.Value, _
>> Len(ActiveCell.Value) - _
>> WorksheetFunction.Find( _
>> "-", _
>> ActiveCell.Value)) - _
>> --Left( _
>> ActiveCell.Value, _
>> WorksheetFunction.Find( _
>> "-", _
>> ActiveCell.Value) - 1) _
>> ).EntireRow.Insert
>> Exit Sub
>> NoHyphen:
>> MsgBox "Invalid Format", vbCritical, "No Hyphen"
>> End Sub
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Sean H" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am looking for a way to insert rows underneath a row, with a macro
>> >reading
>> > a cell, and then inserting that number of rows underneath.
>> >
>> > e.g. A column reads 500-505. The macro will seperate the numbers
>> > (500,505)
>> > into adjecent cells, calculate the difference (5), and then insert that
>> > number of columns underneath the original row (reading "500-505" with 5
>> > blank
>> > rows underneath). I figured out how to do the calculation, but I can't
>> > find
>> > a way to insert the columns. Is there a way to do this? I already
>> > know
>> > the
>> > answer is "yes", but is it feasable for a beginner macro'er to try and
>> > implement?
>> >
>> > Thanks,
>> > Sean H
>>
>>
>>