"Insert" Macro
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" wrote in message
...
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
|