Inserting Rows

[Cameron DeChandt]

I have a worksheet with one column of sorted numerical
data. What I am trying to do is insert 2 rows whenever
the number increases.

example:

COLUMN A
111
111
111
111
empty row
empty row
222
222
222

Any help is greatly appreciated.

Cameron De Chandt

[LHKITTLE]

Hi Cameron,

Give this a go. Works on Column D and only down 100 rows. Change
to suit needs.

Sub RowBeTwo()
Range("D1").Select
Dim Dum As Integer, i As Integer
Dum = Range("D100").End(xlUp).Row
For i = 1 To Dum
If ActiveCell.Offset(1, 0).Value = "" Then Exit Sub
If ActiveCell.Offset(1, 0) > ActiveCell Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(3, 0).Select
Else: ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

HTH
Regards,
Howard

"Cameron DeChandt" <CDec...@mail.de.state.az.us> wrote in
message news:4daf01c14b90$929cfbe0$39ef2ecf@TKMSFTNGXA08...

[Vasant Nanavati]

Hi Cameron:

Select the one-column range that you want to operate on, then run the
following macro (untested but should work):

Sub InsertBlankRows()
Dim i As Integer
For i=Selection.Rows.Count To 1 Step -1
If Selection(i).Row=1 then Exit Sub
If Selection(i)<>Selection(i-1) And Not IsEmpty(Selection(i-1)) Then
Selection(i).Resize(2,1).EntireRow.Insert

End If
Next
End Sub

Regards,

Vasant.

"Cameron DeChandt" <CDec...@mail.de.state.az.us> wrote in message
news:4daf01c14b90$929cfbe0$39ef2ecf@TKMSFTNGXA08...

[David McRitchie]

Vasant's was an improvement using STEP -1 to start from the bottom
when inserting or deleting rows. But it will not work in Excel 2000,
well I just happened to choose an example with over 37,000 rows
in the used range. So here are some more tips to make it more
bulletproof.
You must use LONG instead of Integer in Excel 97 and up which have
65,536 rows and Integer range is from -32,768 to 32,767
The user could select more than one column and you want to restrict
to a single column -- see use of INTERSECT.
Best if you can rerun the same macro again after having run it, you can't
with what you have, so you are checking the wrong cell for blank.
Cameron probably can use not equal; nevertheless, but he did specifically say
to include blank rows when the number increases, so I used > instead of <>

Sub InsertBlankRows()
Dim i As Long 'integer limited range -32,768 to 32,767 not sufficient
Dim nRange As Range
Set nRange = Intersect(Selection, ActiveSheet.UsedRange, _
ActiveCell.EntireColumn)
If nRange.Cells.Count < 2 Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = nRange.Cells.Count To 2 Step -1
If nRange.Item(i) > nRange.Item(i - 1) And _
Not IsEmpty(nRange.Item(i - 1)) Then
nRange.Item(i).Resize(2, 1).EntireRow.Insert
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH, This example will eventually appear in
http://www.mvps.org/dmcritchie/excel/proper.htm
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Vasant Nanavati" <vas...@aol.com> wrote in message news:u7GL8u5SBHA.2040@tkmsftngp05...

> Hi Cameron:
>
> Select the one-column range that you want to operate on, then run the
> following macro (untested but should work):
>
> Sub InsertBlankRows()
> Dim i As Integer
> For i=Selection.Rows.Count To 1 Step -1
> If Selection(i).Row=1 then Exit Sub
> If Selection(i)<>Selection(i-1) And Not IsEmpty(Selection(i-1)) Then
> Selection(i).Resize(2,1).EntireRow.Insert
> End If
> Next
> End Sub
>

[Vasant Nanavati]

Hi Dave:

>>tips to make it more bulletproof<<

All good points! I note that you have already added the macro to your site.

In the interest of further bulletproofing, you might want to add a trap for
type mismatch errors in the event that some of the cells contain text.

Regards,

Vasant.

David McRitchie <dmcri...@msn.com> wrote in message
news:edDvu27SBHA.1124@tkmsftngp05...

[David McRitchie]

Hi Vasant,
Note I didn't include that word "number" on my site. I wouldn't know
what to do if it is not a number, so left it for Cameron to indicate whether
solution wasn't good enough. I actually tested with a mixture of original
cell addresses (MarkCells macro) and dates and didn't look for
interactions between text and numbers.

Not about to stop the macro because a non number was found.
It works for all numeric data in cells and it works equal well for
all text data in cells in the range. It would be easy to turn text
constants green, for instance (hope that wouldn't be taken seriously)..

HTH,

David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Vasant Nanavati" <vas...@aol.com> wrote in message news:uK9L1$DTBHA.1424@tkmsftngp03...

[Vasant Nanavati]

>>It would be easy to turn text constants green, for instance (hope that
wouldn't be taken seriously).<<

Be a nice touch, though! :)

"David McRitchie" <dmcri...@msn.com> wrote in message

news:eQJsZMHTBHA.920@tkmsftngp03...