Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Insert Blank Rows
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 44
Default Insert Blank Rows
How do I insert one or multiple blanks rows after each type of data? The
insert criteria is based on the data in Column D, not column A.

Cloumn A B C D E

28FEB07 2006 Q1-Q4 Charge 23.39
31MAR07 2006 Q1-Q4 Charge 23.39
30APR07 2006 Q1-Q4 Charge 30.04
31MAY07 2006 Q1-Q4 Charge 30.04
30JUN07 2006 Q1-Q4 Charge 30.04
31JUL07 2006 Q1-Q4 Charge 30.04
31AUG07 2006 Q1-Q4 Charge 30.04
30SEP07 2006 Q1-Q4 Charge 30.04
29FEB08 2007 Q1-Q4 Charge 8.37
09MAY08 2008 Q1-Q4 Interest 38.69
13JUN08 2008 Q1-Q4 Interest 48.63
11JUL08 2008 Q1-Q4 Interest 49.58
15AUG08 2008 Q1-Q4 Interest 45.61
12SEP08 2008 Q1-Q4 Interest 42.61
10OCT08 2008 Q1-Q4 Interest 46.19
07NOV07 2006 Q1-Q4 Interest 487.01
26AUG08 2007 Q1-Q4 Interest 422.9
16DEC05 2005 Q1-Q4 Principal 300
26SEP06 2006 Q1-Q4 Principal 1751.34
26SEP06 2006 Q1-Q4 Principal 588.15
06NOV07 2007 Q3 Principal 836.74
26AUG08 2008 Q1 Principal 568.13
26AUG08 2008 Q2 Principal 740.34



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Insert Blank Rows
Hi,

Right click your sheet tab, view code and paste this and run it.

Sub insertem()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 4).Value < Cells(x, 4).Offset(-1, 0).Value Then
Rows(x).EntireRow.Insert
End If
Next
End Sub

Mike

"igbert" wrote:

How do I insert one or multiple blanks rows after each type of data? The
insert criteria is based on the data in Column D, not column A.

Cloumn A B C D E

28FEB07 2006 Q1-Q4 Charge 23.39
31MAR07 2006 Q1-Q4 Charge 23.39
30APR07 2006 Q1-Q4 Charge 30.04
31MAY07 2006 Q1-Q4 Charge 30.04
30JUN07 2006 Q1-Q4 Charge 30.04
31JUL07 2006 Q1-Q4 Charge 30.04
31AUG07 2006 Q1-Q4 Charge 30.04
30SEP07 2006 Q1-Q4 Charge 30.04
29FEB08 2007 Q1-Q4 Charge 8.37
09MAY08 2008 Q1-Q4 Interest 38.69
13JUN08 2008 Q1-Q4 Interest 48.63
11JUL08 2008 Q1-Q4 Interest 49.58
15AUG08 2008 Q1-Q4 Interest 45.61
12SEP08 2008 Q1-Q4 Interest 42.61
10OCT08 2008 Q1-Q4 Interest 46.19
07NOV07 2006 Q1-Q4 Interest 487.01
26AUG08 2007 Q1-Q4 Interest 422.9
16DEC05 2005 Q1-Q4 Principal 300
26SEP06 2006 Q1-Q4 Principal 1751.34
26SEP06 2006 Q1-Q4 Principal 588.15
06NOV07 2007 Q3 Principal 836.74
26AUG08 2008 Q1 Principal 568.13
26AUG08 2008 Q2 Principal 740.34



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Insert Blank Rows
Just noticed you wanted to insert variable rows. try this instead

Sub insertem()
numrows = InputBox("How many rows to insert?")
If Not IsNumeric(numrows) Then Exit Sub
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 4).Value < Cells(x, 4).Offset(-1, 0).Value Then
For i = 1 To numrows
Rows(x).EntireRow.Insert
Next
End If
Next
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this and run it.

Sub insertem()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 4).Value < Cells(x, 4).Offset(-1, 0).Value Then
Rows(x).EntireRow.Insert
End If
Next
End Sub

Mike

"igbert" wrote:

How do I insert one or multiple blanks rows after each type of data? The
insert criteria is based on the data in Column D, not column A.

Cloumn A B C D E

28FEB07 2006 Q1-Q4 Charge 23.39
31MAR07 2006 Q1-Q4 Charge 23.39
30APR07 2006 Q1-Q4 Charge 30.04
31MAY07 2006 Q1-Q4 Charge 30.04
30JUN07 2006 Q1-Q4 Charge 30.04
31JUL07 2006 Q1-Q4 Charge 30.04
31AUG07 2006 Q1-Q4 Charge 30.04
30SEP07 2006 Q1-Q4 Charge 30.04
29FEB08 2007 Q1-Q4 Charge 8.37
09MAY08 2008 Q1-Q4 Interest 38.69
13JUN08 2008 Q1-Q4 Interest 48.63
11JUL08 2008 Q1-Q4 Interest 49.58
15AUG08 2008 Q1-Q4 Interest 45.61
12SEP08 2008 Q1-Q4 Interest 42.61
10OCT08 2008 Q1-Q4 Interest 46.19
07NOV07 2006 Q1-Q4 Interest 487.01
26AUG08 2007 Q1-Q4 Interest 422.9
16DEC05 2005 Q1-Q4 Principal 300
26SEP06 2006 Q1-Q4 Principal 1751.34
26SEP06 2006 Q1-Q4 Principal 588.15
06NOV07 2007 Q3 Principal 836.74
26AUG08 2008 Q1 Principal 568.13
26AUG08 2008 Q2 Principal 740.34



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,805
Default Insert Blank Rows
Use this macro on acitve sheet
See comments within the code

Sub insertblankrows()
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Key = .Cells(lastRow, 4).Value
lastRow = lastRow - 1

'Change 2 to 1 if you don't have header row
For i = lastRow To 2 Step -1
If .Cells(i, 4).Value < Key Then
'Change 1:1 to 1:2 if you want to insert two rows each
Cells(i + 1, 1).Rows("1:1").EntireRow.Insert
Key = .Cells(i, 4).Value
End If
Next i
End With
End Sub


"igbert" wrote:

How do I insert one or multiple blanks rows after each type of data? The
insert criteria is based on the data in Column D, not column A.

Cloumn A B C D E

28FEB07 2006 Q1-Q4 Charge 23.39
31MAR07 2006 Q1-Q4 Charge 23.39
30APR07 2006 Q1-Q4 Charge 30.04
31MAY07 2006 Q1-Q4 Charge 30.04
30JUN07 2006 Q1-Q4 Charge 30.04
31JUL07 2006 Q1-Q4 Charge 30.04
31AUG07 2006 Q1-Q4 Charge 30.04
30SEP07 2006 Q1-Q4 Charge 30.04
29FEB08 2007 Q1-Q4 Charge 8.37
09MAY08 2008 Q1-Q4 Interest 38.69
13JUN08 2008 Q1-Q4 Interest 48.63
11JUL08 2008 Q1-Q4 Interest 49.58
15AUG08 2008 Q1-Q4 Interest 45.61
12SEP08 2008 Q1-Q4 Interest 42.61
10OCT08 2008 Q1-Q4 Interest 46.19
07NOV07 2006 Q1-Q4 Interest 487.01
26AUG08 2007 Q1-Q4 Interest 422.9
16DEC05 2005 Q1-Q4 Principal 300
26SEP06 2006 Q1-Q4 Principal 1751.34
26SEP06 2006 Q1-Q4 Principal 588.15
06NOV07 2007 Q3 Principal 836.74
26AUG08 2008 Q1 Principal 568.13
26AUG08 2008 Q2 Principal 740.34



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Insert Blank Rows
Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 4).Value < Cells(X - 1, 4).Value Then
If Cells(X, 4).Value < "" Then
If Cells(X - 1, 4).Value < "" Then
Cells(X, 4).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 24 Oct 2008 12:32:02 -0700, igbert
wrote:

How do I insert one or multiple blanks rows after each type of data? The
insert criteria is based on the data in Column D, not column A.

Cloumn A B C D E

28FEB07 2006 Q1-Q4 Charge 23.39
31MAR07 2006 Q1-Q4 Charge 23.39
30APR07 2006 Q1-Q4 Charge 30.04
31MAY07 2006 Q1-Q4 Charge 30.04
30JUN07 2006 Q1-Q4 Charge 30.04
31JUL07 2006 Q1-Q4 Charge 30.04
31AUG07 2006 Q1-Q4 Charge 30.04
30SEP07 2006 Q1-Q4 Charge 30.04
29FEB08 2007 Q1-Q4 Charge 8.37
09MAY08 2008 Q1-Q4 Interest 38.69
13JUN08 2008 Q1-Q4 Interest 48.63
11JUL08 2008 Q1-Q4 Interest 49.58
15AUG08 2008 Q1-Q4 Interest 45.61
12SEP08 2008 Q1-Q4 Interest 42.61
10OCT08 2008 Q1-Q4 Interest 46.19
07NOV07 2006 Q1-Q4 Interest 487.01
26AUG08 2007 Q1-Q4 Interest 422.9
16DEC05 2005 Q1-Q4 Principal 300
26SEP06 2006 Q1-Q4 Principal 1751.34
26SEP06 2006 Q1-Q4 Principal 588.15
06NOV07 2007 Q3 Principal 836.74
26AUG08 2008 Q1 Principal 568.13
26AUG08 2008 Q2 Principal 740.34



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 44
Default Insert Blank Rows
Thanks,

Your answer works perfect. It is flexible to add whatever row(s) I want.
Many thanks for the great solution.

Igbert

"Mike H" wrote:

Just noticed you wanted to insert variable rows. try this instead

Sub insertem()
numrows = InputBox("How many rows to insert?")
If Not IsNumeric(numrows) Then Exit Sub
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 4).Value < Cells(x, 4).Offset(-1, 0).Value Then
For i = 1 To numrows
Rows(x).EntireRow.Insert
Next
End If
Next
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this and run it.

Sub insertem()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 4).Value < Cells(x, 4).Offset(-1, 0).Value Then
Rows(x).EntireRow.Insert
End If
Next
End Sub

Mike

"igbert" wrote:

How do I insert one or multiple blanks rows after each type of data? The
insert criteria is based on the data in Column D, not column A.

Cloumn A B C D E

28FEB07 2006 Q1-Q4 Charge 23.39
31MAR07 2006 Q1-Q4 Charge 23.39
30APR07 2006 Q1-Q4 Charge 30.04
31MAY07 2006 Q1-Q4 Charge 30.04
30JUN07 2006 Q1-Q4 Charge 30.04
31JUL07 2006 Q1-Q4 Charge 30.04
31AUG07 2006 Q1-Q4 Charge 30.04
30SEP07 2006 Q1-Q4 Charge 30.04
29FEB08 2007 Q1-Q4 Charge 8.37
09MAY08 2008 Q1-Q4 Interest 38.69
13JUN08 2008 Q1-Q4 Interest 48.63
11JUL08 2008 Q1-Q4 Interest 49.58
15AUG08 2008 Q1-Q4 Interest 45.61
12SEP08 2008 Q1-Q4 Interest 42.61
10OCT08 2008 Q1-Q4 Interest 46.19
07NOV07 2006 Q1-Q4 Interest 487.01
26AUG08 2007 Q1-Q4 Interest 422.9
16DEC05 2005 Q1-Q4 Principal 300
26SEP06 2006 Q1-Q4 Principal 1751.34
26SEP06 2006 Q1-Q4 Principal 588.15
06NOV07 2007 Q3 Principal 836.74
26AUG08 2008 Q1 Principal 568.13
26AUG08 2008 Q2 Principal 740.34



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to insert blank rows Gaurav[_3_] Excel Worksheet Functions 2 June 26th 08 07:55 PM
How do i insert blank rows between data that is thousands of rows paul.eatwell Excel Discussion (Misc queries) 5 April 14th 08 10:49 PM
insert blank rows sillyscorpio Excel Discussion (Misc queries) 2 December 7th 07 03:23 PM
How can I insert a blank row after every four rows in Excel? EmmaSB Excel Discussion (Misc queries) 2 April 20th 07 10:10 PM
How do I insert blank rows between rows in completed worksheet? bblue1978 Excel Discussion (Misc queries) 1 October 26th 06 07:02 PM


All times are GMT +1. The time now is 11:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement