Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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 ** "Lost" wrote in message ... 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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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 "Lost" wrote in message ... 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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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 "Bernie Deitrick" wrote in message ... 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 "Lost" wrote in message ... 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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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? -----Original Message----- 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 "Lost" wrote in message ... 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... . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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 "Lost" wrote in message ... 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? -----Original Message----- 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 "Lost" wrote in message ... 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... . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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" "Bernie Deitrick" wrote in message ... 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 "Lost" wrote in message ... 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? -----Original Message----- 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 "Lost" wrote in message ... 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... . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
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 "Paul B" wrote in message ... 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" "Bernie Deitrick" wrote in message ... 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 "Lost" wrote in message ... 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? -----Original Message----- 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 "Lost" wrote in message ... 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... . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows
Bernie, that works with the data sorted, thanks for the explanation
"Bernie Deitrick" wrote in message ... 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 "Paul B" wrote in message ... 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" "Bernie Deitrick" wrote in message ... 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 "Lost" wrote in message ... 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? -----Original Message----- 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 "Lost" wrote in message ... 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... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) |