Insert rows

  • Thread starter Thread starter Lost
  • Start date Start date
L

Lost

I hope someone can help me with this confusing thing.

I have a large spreadsheet (20,000 rows +) and I have a
date in column R. The sheet has been sorted on column R.

I want the most efficient code I can find to insert two
rows everytime the a different date is found in column R.

Please supply your example code...and thanks millions...
 
Lost, don't know if this is the most efficient, but it works :) this code is
modified form one written by Barrie Davidson

Sub Insert_row_in_R()
Dim Number_of_rows As Long
Dim Rowinsert As Integer
Application.ScreenUpdating = False
Number_of_rows = Range("R65536").End(xlUp).Row
Rowinsert = 2
Range("R2").Select
Do Until Selection.Row = Number_of_rows + 1
If Selection.Value <> Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Resize(Rowinsert).Insert
Number_of_rows = Number_of_rows + Rowinsert
Selection.Offset(Rowinsert + 1, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Dear Lost,

The code below will insert two blank rows at every change in value in
column B. To use a different column, change the myCol = 2 to an
appropriate number.

This also assumes you have headers in Row 1.

This code uses the fastest way to insert rows: sorting using Excel's
built-in sorting routines. Other code is possible that would step
cell-wise through your range of data, but inserting mulitple pairs of
rows can be quite slow on large files.

HTH,
Bernie
MS Excel MVP

Sub Insert2BlankColumns()
Dim myCell1 As Range
Dim myCell2 As Range
Dim myCol As Integer
Dim myRow As Long

'Change these two variables as needed
myCol = 2 'For column B
myRow = 1 'Row with labels

Set myCell1 = Cells(Rows.Count, myCol).End(xlUp)
Range(Cells(myRow, myCol), myCell1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=myCell1(2), _
Unique:=True
myCell1(2).EntireRow.Delete
Set myCell2 = Cells(Rows.Count, myCol).End(xlUp)
Range(myCell1(2), myCell2).Copy myCell2(2)

Cells(myRow, myCol).CurrentRegion.Sort _
Key1:=Cells(myRow, myCol), _
Order1:=xlAscending, _
Header:=xlYes

For Each myCell1 In Range(Cells(myRow, myCol), _
Cells(Rows.Count, myCol).End(xlUp))
If myCell1(1, 0).Value = "" Then myCell1.ClearContents
Next myCell1
End Sub
 
In case anyone is interested in the performance differences, with
20,000 rows, 365 unique values in the date column, and 10 total
columns of data, my routine took 1.43 secs, and Paul's took 72.94
secs - about 50 times longer.

HTH,
Bernie
MS Excel MVP
 
Thanks, but I can't get it to work...I need to insert
entire rows in between dates...

Your code seems to jumble everything up? What am I doing
wrong? My dates are in column R, so I exchanged your 2 for
my column 18...any clues?
 
Dear Lost,

Is your data range contiguous, or does it already have blank rows in it? Do
you have blank columns - or hidden blank columns? Do you have cells beneath
your data range that have values? Try selecting cell R1 and then use Edit
Go To... Special, Current Region and see what area is selected.

The code worked perfectly with my test spreadsheet - if you can't figure it
out based on my guesses above, then email the workbook to me and I'll see
what I can see.

HTH,
Bernie
MS Excel MVP
 
Bernie, I also tried your coded, because it was so much faster, I also could
not get it to do what the OP wanted, it adds row but puts the data where it
inserted the rows at the bottom of the data. As lost says "seems to jumble
everything up"
 
Paul (and Lost),

The original post said that the data was sorted based on the date. I
assumed that the data was sorted ascending, but if both of those
things aren't true (sorted and sorted ascending), then the final sort
ascending (which is the step that puts the blank rows into the data
base) will change the order of the original data set rather than just
inserting blank lines.

If that doesn't work, send me your sample worksheet and I will take a
look.

HTH,
Bernie
MS Excel MVP
 
Back
Top