Formula for inserting blank row
 

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie

Formula for inserting blank row
 

There's a few steps you can follow to get where you want to be:

FIRST AND FOREMOST - WORK ON A COPY OF YOUR SPREADSHEET

If your data do not have column headings, insert a new row immediately above
the data and add headers.
With the cursor in your data, use Data-Subtotals to have insert a subtotal
on each change in your number column
When it finishes adding the subtotals, click on the little #2 button just
under the address box to display just the subtotals
Select the entire displayed range
Use Edit-Goto-Special and click on Visible Cells Only
Delete those rows
Click on the #3 button

"Connie Martin" wrote:

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie

Formula for inserting blank row
 

That's neat! I use the subtotal feature but never knew you could use it this
way! Thank you!

"Duke Carey" wrote:

There's a few steps you can follow to get where you want to be:

FIRST AND FOREMOST - WORK ON A COPY OF YOUR SPREADSHEET

If your data do not have column headings, insert a new row immediately above
the data and add headers.
With the cursor in your data, use Data-Subtotals to have insert a subtotal
on each change in your number column
When it finishes adding the subtotals, click on the little #2 button just
under the address box to display just the subtotals
Select the entire displayed range
Use Edit-Goto-Special and click on Visible Cells Only
Delete those rows
Click on the #3 button

"Connie Martin" wrote:

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie

Formula for inserting blank row
 

Connie

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

As written, operates on Column A


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin"
wrote:

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie

Formula for inserting blank row
 

Works like a charm! Now, if the data were in another column other than A,
what do I change in this macro. I don't see any reference to column A but
it's probably there written in another lanugage! Connie

"Gord Dibben" wrote:

Connie

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

As written, operates on Column A


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin"
wrote:

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie

Formula for inserting blank row
 

Connie

Changes made to operate on Column C(3)

For i = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 3) < Cells(i, 3) Then _
Cells(i, 3).Resize(1, 1).EntireRow.Insert

Or this to choose a column number.

Sub InsertRow_At_Change()
Dim i As Long
Dim colno As Long
colno = InputBox("Enter a Column Number")
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1
If Cells(i - 1, colno) < Cells(i, colno) Then _
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord


On Thu, 17 Nov 2005 12:11:04 -0800, "Connie Martin"
wrote:

Works like a charm! Now, if the data were in another column other than A,
what do I change in this macro. I don't see any reference to column A but
it's probably there written in another lanugage! Connie

"Gord Dibben" wrote:

Connie

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

As written, operates on Column A


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin"
wrote:

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie

Formula for inserting blank row
 

Thank you very much. This works so slick! With a worksheet of sometimes
1000 rows long, what a pile of work this macro saves. Thanks again! Connie

"Gord Dibben" wrote:

Connie

Changes made to operate on Column C(3)

For i = Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 3) < Cells(i, 3) Then _
Cells(i, 3).Resize(1, 1).EntireRow.Insert

Or this to choose a column number.

Sub InsertRow_At_Change()
Dim i As Long
Dim colno As Long
colno = InputBox("Enter a Column Number")
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1
If Cells(i - 1, colno) < Cells(i, colno) Then _
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord


On Thu, 17 Nov 2005 12:11:04 -0800, "Connie Martin"
wrote:

Works like a charm! Now, if the data were in another column other than A,
what do I change in this macro. I don't see any reference to column A but
it's probably there written in another lanugage! Connie

"Gord Dibben" wrote:

Connie

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

As written, operates on Column A


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 08:45:11 -0800, "Connie Martin"
wrote:

Is there a formula or macro that would automatically insert a blank row right
after a number repeats itself? The repeating numbers are in one column.
This is a huge spreadsheet and a real pain to do this manually. Connie