insert rows between sequence gaps

jacob128

New Member
Joined
Dec 17, 2011
Messages
22
I recently found a thread with the same name (http://www.mrexcel.com/forum/showthread.php?t=420750), and two people had posted codes which were said to insert rows between sequence gaps, i.e. making 1,2,4,5 : 1,2,3,4,5 in column b (for example), but when I have tried using the codes, neither actually do anything to the active sheet. anyone know why this may be?

Code 1: Zack Barresse

Code:
Sub ExpandDataPlease()    

Dim i As Long, iRow As Long     
With ActiveSheet         
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1             
.Rows(i).Cut .Rows(.Cells(i, 1).Value)         
Next i     
End With 
End Sub
Code 2: Texasalynn

Code:
Sub insertSeqRows() 
Dim LR As Long, i As Long, x As Long 
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' get last row 
For i = LR To 2 Step -1    
x = Cells(i, "A").Value - Cells(i - 1, "A").Value       
If x <> 1 Then         
Rows(i).Resize(x - 1).EntireRow.Insert     
End If      
Next i 
End Sub

(both formatted properly in in other thread)
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
codes which were said to insert rows between sequence gaps, i.e. making 1,2,4,5 : 1,2,3,4,5 in column b (for example)

The two macros insert rows based on the sequence in column A. Your question implies your sequence is in column B.
 
Upvote 0
according to zack:
It also assumes your data is in cols A & B (col A houses a number of the destination row, col B houses your values)
which is how i've set it up... (and i get the error type:mismatch if i dont have them set up as quoted above)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top