How do I automatically insert a blank row between sets of data?
 

I have information I copy from one database from another. The rows all start
with the chassis number. There could be 30 chassis in that week. I want to
have excell automatically insert a blank row between each chassis
information. There are sometimes only one row of data related to that
chassis and sometimes 20 or 30, never a set number. Can this be done?

How do I automatically insert a blank row between sets of data?
 

"Automatic" would need VBA.

Here is a macro that inserts a row at every change in chassis number in column A

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


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 07:16:03 -0700, usdivers
wrote:

I have information I copy from one database from another. The rows all start
with the chassis number. There could be 30 chassis in that week. I want to
have excell automatically insert a blank row between each chassis
information. There are sometimes only one row of data related to that
chassis and sometimes 20 or 30, never a set number. Can this be done?

How do I automatically insert a blank row between sets of data
 

Gord, Thank you SO MUCH. That worked perfectly!

"Gord Dibben" wrote:

"Automatic" would need VBA.

Here is a macro that inserts a row at every change in chassis number in column A

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


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 07:16:03 -0700, usdivers
wrote:

I have information I copy from one database from another. The rows all start
with the chassis number. There could be 30 chassis in that week. I want to
have excell automatically insert a blank row between each chassis
information. There are sometimes only one row of data related to that
chassis and sometimes 20 or 30, never a set number. Can this be done?

How do I automatically insert a blank row between sets of data
 

Glad to help.

Thanks for the feedback.


Gord

On Thu, 17 Aug 2006 17:14:01 -0700, usdivers
wrote:

Gord, Thank you SO MUCH. That worked perfectly!

"Gord Dibben" wrote:

"Automatic" would need VBA.

Here is a macro that inserts a row at every change in chassis number in column A

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


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 07:16:03 -0700, usdivers
wrote:

I have information I copy from one database from another. The rows all start
with the chassis number. There could be 30 chassis in that week. I want to
have excell automatically insert a blank row between each chassis
information. There are sometimes only one row of data related to that
chassis and sometimes 20 or 30, never a set number. Can this be done?

How do I automatically insert a blank row between sets of data
 

Hi Gord Dibben,

How about inserting two rows? Please help

Regards
Giovs


"Gord Dibben" wrote:

Glad to help.

Thanks for the feedback.


Gord

On Thu, 17 Aug 2006 17:14:01 -0700, usdivers
wrote:

Gord, Thank you SO MUCH. That worked perfectly!

"Gord Dibben" wrote:

"Automatic" would need VBA.

Here is a macro that inserts a row at every change in chassis number in column A

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


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 07:16:03 -0700, usdivers
wrote:

I have information I copy from one database from another. The rows all start
with the chassis number. There could be 30 chassis in that week. I want to
have excell automatically insert a blank row between each chassis
information. There are sometimes only one row of data related to that
chassis and sometimes 20 or 30, never a set number. Can this be done?

How do I automatically insert a blank row between sets of data
 

Try changing
Cells(i, 1).Resize(1, 1).EntireRow.Insert
to
Cells(i, 1).Resize(2, 1).EntireRow.Insert

Giovs wrote:

Hi Gord Dibben,

How about inserting two rows? Please help

Regards
Giovs

"Gord Dibben" wrote:

Glad to help.

Thanks for the feedback.


Gord

On Thu, 17 Aug 2006 17:14:01 -0700, usdivers
wrote:

Gord, Thank you SO MUCH. That worked perfectly!

"Gord Dibben" wrote:

"Automatic" would need VBA.

Here is a macro that inserts a row at every change in chassis number in column A

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


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 07:16:03 -0700, usdivers
wrote:

I have information I copy from one database from another. The rows all start
with the chassis number. There could be 30 chassis in that week. I want to
have excell automatically insert a blank row between each chassis
information. There are sometimes only one row of data related to that
chassis and sometimes 20 or 30, never a set number. Can this be done?





--

Dave Peterson