| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert new rows based on Data in other rows
Hi
I have a bit of a nightmare task on to carry out on large worksheets that I am hoping can be automated in some way because manually this will take far too long The worksheets basically look like this... -------A------- -------B------- ------C------- -------D------- 1 ---61236----- ---61239----- - a text val -- - a text val -- 2 ---61531----- ---61531----- - B text val -- - B text val -- The number in column A and the number in column B represent a range for which the values in the other columns apply. (So if a given number is between A and B then C and D are true). What I need to do is change this sheet so that instead of giving the number range in columns A and B, it gives each number individually. (Instead of A saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with all other values applicable copied down the sheet.) The example abopve would end up looking something like this.. -------A------- ------B------- -------C------- 1 ---61236----- - a text val -- - a text val -- 2 ---61237----- - a text val -- - a text val -- 3 ---61238----- - a text val -- - a text val -- 4 ---61239----- - a text val -- - a text val -- 5 ---61531----- - B text val -- - B text val -- Is this possible or would it require some advanced programming or similar? Thanks in advance for any help. Regards John |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert new rows based on Data in other rows
John,
You could use a macro: try the macro below. It worked well in my tests. HTH, Bernie MS Excel MVP Sub JohnRowInsertMacro() Dim myRow As Long Dim myCount As Integer For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value If myCount 0 Then Rows(myRow).Copy Rows(myRow + 1).Resize(myCount).Insert With Cells(myRow + 1, 1).Resize(myCount) .FormulaR1C1 = "=R[-1]C+1" .Value = .Value End With End If End If Next myRow Columns(2).Delete End Sub "mg_sv_r" wrote in message ... Hi I have a bit of a nightmare task on to carry out on large worksheets that I am hoping can be automated in some way because manually this will take far too long The worksheets basically look like this... -------A------- -------B------- ------C------- -------D------- 1 ---61236----- ---61239----- - a text val -- - a text val -- 2 ---61531----- ---61531----- - B text val -- - B text val -- The number in column A and the number in column B represent a range for which the values in the other columns apply. (So if a given number is between A and B then C and D are true). What I need to do is change this sheet so that instead of giving the number range in columns A and B, it gives each number individually. (Instead of A saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with all other values applicable copied down the sheet.) The example abopve would end up looking something like this.. -------A------- ------B------- -------C------- 1 ---61236----- - a text val -- - a text val -- 2 ---61237----- - a text val -- - a text val -- 3 ---61238----- - a text val -- - a text val -- 4 ---61239----- - a text val -- - a text val -- 5 ---61531----- - B text val -- - B text val -- Is this possible or would it require some advanced programming or similar? Thanks in advance for any help. Regards John |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert new rows based on Data in other rows
Bernie,
You are genius. Your marco has done 5 sheets containing about 15k rows of data on each in about 5 minutes! Without this, I doubt I would have got this done! Thank you very much! John "Bernie Deitrick" wrote: John, You could use a macro: try the macro below. It worked well in my tests. HTH, Bernie MS Excel MVP Sub JohnRowInsertMacro() Dim myRow As Long Dim myCount As Integer For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value If myCount 0 Then Rows(myRow).Copy Rows(myRow + 1).Resize(myCount).Insert With Cells(myRow + 1, 1).Resize(myCount) .FormulaR1C1 = "=R[-1]C+1" .Value = .Value End With End If End If Next myRow Columns(2).Delete End Sub "mg_sv_r" wrote in message ... Hi I have a bit of a nightmare task on to carry out on large worksheets that I am hoping can be automated in some way because manually this will take far too long The worksheets basically look like this... -------A------- -------B------- ------C------- -------D------- 1 ---61236----- ---61239----- - a text val -- - a text val -- 2 ---61531----- ---61531----- - B text val -- - B text val -- The number in column A and the number in column B represent a range for which the values in the other columns apply. (So if a given number is between A and B then C and D are true). What I need to do is change this sheet so that instead of giving the number range in columns A and B, it gives each number individually. (Instead of A saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with all other values applicable copied down the sheet.) The example abopve would end up looking something like this.. -------A------- ------B------- -------C------- 1 ---61236----- - a text val -- - a text val -- 2 ---61237----- - a text val -- - a text val -- 3 ---61238----- - a text val -- - a text val -- 4 ---61239----- - a text val -- - a text val -- 5 ---61531----- - B text val -- - B text val -- Is this possible or would it require some advanced programming or similar? Thanks in advance for any help. Regards John |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert new rows based on Data in other rows
You are genius. Your marco has done 5 sheets containing about 15k rows of
data on each in about 5 minutes! <Blush I didn't realize that you has such big data sets. It might be faster if you used this at the top of the code: With Application .ScreeenUpdating = False .EnableEvents = False End With and this at the bottom of the code With Application .ScreeenUpdating = True .EnableEvents = True End With And it might be faster still if we turned off automatic calculation, and filled in the numbers using code instead of the formula... HTH, Bernie MS Excel MVP "mg_sv_r" wrote in message ... Bernie, You are genius. Your marco has done 5 sheets containing about 15k rows of data on each in about 5 minutes! Without this, I doubt I would have got this done! Thank you very much! John "Bernie Deitrick" wrote: John, You could use a macro: try the macro below. It worked well in my tests. HTH, Bernie MS Excel MVP Sub JohnRowInsertMacro() Dim myRow As Long Dim myCount As Integer For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value If myCount 0 Then Rows(myRow).Copy Rows(myRow + 1).Resize(myCount).Insert With Cells(myRow + 1, 1).Resize(myCount) .FormulaR1C1 = "=R[-1]C+1" .Value = .Value End With End If End If Next myRow Columns(2).Delete End Sub "mg_sv_r" wrote in message ... Hi I have a bit of a nightmare task on to carry out on large worksheets that I am hoping can be automated in some way because manually this will take far too long The worksheets basically look like this... -------A------- -------B------- ------C------- -------D------- 1 ---61236----- ---61239----- - a text val -- - a text val -- 2 ---61531----- ---61531----- - B text val -- - B text val -- The number in column A and the number in column B represent a range for which the values in the other columns apply. (So if a given number is between A and B then C and D are true). What I need to do is change this sheet so that instead of giving the number range in columns A and B, it gives each number individually. (Instead of A saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with all other values applicable copied down the sheet.) The example abopve would end up looking something like this.. -------A------- ------B------- -------C------- 1 ---61236----- - a text val -- - a text val -- 2 ---61237----- - a text val -- - a text val -- 3 ---61238----- - a text val -- - a text val -- 4 ---61239----- - a text val -- - a text val -- 5 ---61531----- - B text val -- - B text val -- Is this possible or would it require some advanced programming or similar? Thanks in advance for any help. Regards John |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert new rows based on Data in other rows
Bernie,
Now you are just showing off ;) Thank you, the five minutes was more than acceptable but I will give your suggestion a go next month when I get more of these sheets to work on. Thank you very much for your help. John "Bernie Deitrick" wrote: You are genius. Your marco has done 5 sheets containing about 15k rows of data on each in about 5 minutes! <Blush I didn't realize that you has such big data sets. It might be faster if you used this at the top of the code: With Application .ScreeenUpdating = False .EnableEvents = False End With and this at the bottom of the code With Application .ScreeenUpdating = True .EnableEvents = True End With And it might be faster still if we turned off automatic calculation, and filled in the numbers using code instead of the formula... HTH, Bernie MS Excel MVP "mg_sv_r" wrote in message ... Bernie, You are genius. Your marco has done 5 sheets containing about 15k rows of data on each in about 5 minutes! Without this, I doubt I would have got this done! Thank you very much! John "Bernie Deitrick" wrote: John, You could use a macro: try the macro below. It worked well in my tests. HTH, Bernie MS Excel MVP Sub JohnRowInsertMacro() Dim myRow As Long Dim myCount As Integer For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value If myCount 0 Then Rows(myRow).Copy Rows(myRow + 1).Resize(myCount).Insert With Cells(myRow + 1, 1).Resize(myCount) .FormulaR1C1 = "=R[-1]C+1" .Value = .Value End With End If End If Next myRow Columns(2).Delete End Sub "mg_sv_r" wrote in message ... Hi I have a bit of a nightmare task on to carry out on large worksheets that I am hoping can be automated in some way because manually this will take far too long The worksheets basically look like this... -------A------- -------B------- ------C------- -------D------- 1 ---61236----- ---61239----- - a text val -- - a text val -- 2 ---61531----- ---61531----- - B text val -- - B text val -- The number in column A and the number in column B represent a range for which the values in the other columns apply. (So if a given number is between A and B then C and D are true). What I need to do is change this sheet so that instead of giving the number range in columns A and B, it gives each number individually. (Instead of A saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with all other values applicable copied down the sheet.) The example abopve would end up looking something like this.. -------A------- ------B------- -------C------- 1 ---61236----- - a text val -- - a text val -- 2 ---61237----- - a text val -- - a text val -- 3 ---61238----- - a text val -- - a text val -- 4 ---61239----- - a text val -- - a text val -- 5 ---61531----- - B text val -- - B text val -- Is this possible or would it require some advanced programming or similar? Thanks in advance for any help. Regards John |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert new rows based on Data in other rows
Hi All,
Been using this macro for a while now without problem Sub JohnRowInsertMacro() Dim myRow As Long Dim myCount As Integer For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value If myCount 0 Then Rows(myRow).Copy Rows(myRow + 1).Resize(myCount).Insert With Cells(myRow + 1, 1).Resize(myCount) .FormulaR1C1 = "=R[-1]C+1" .Value = .Value End With End If End If Next myRow Columns(2).Delete End Sub but now it appears my datasets have outgrown this and it produces a 400 error when the worksheet is full so I am looking for a way to get the macro to write the data to another worksheet instead of the exisiting one and then start a new sheet each time its reaches the end of a sheet. Anyone got any suggestions? Any help would be much appreciated. Regards John "mg_sv_r" wrote: Bernie, Now you are just showing off ;) Thank you, the five minutes was more than acceptable but I will give your suggestion a go next month when I get more of these sheets to work on. Thank you very much for your help. John "Bernie Deitrick" wrote: You are genius. Your marco has done 5 sheets containing about 15k rows of data on each in about 5 minutes! <Blush I didn't realize that you has such big data sets. It might be faster if you used this at the top of the code: With Application .ScreeenUpdating = False .EnableEvents = False End With and this at the bottom of the code With Application .ScreeenUpdating = True .EnableEvents = True End With And it might be faster still if we turned off automatic calculation, and filled in the numbers using code instead of the formula... HTH, Bernie MS Excel MVP "mg_sv_r" wrote in message ... Bernie, You are genius. Your marco has done 5 sheets containing about 15k rows of data on each in about 5 minutes! Without this, I doubt I would have got this done! Thank you very much! John "Bernie Deitrick" wrote: John, You could use a macro: try the macro below. It worked well in my tests. HTH, Bernie MS Excel MVP Sub JohnRowInsertMacro() Dim myRow As Long Dim myCount As Integer For myRow = Range("A65536").End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(myRow, 1).Value) Then myCount = Cells(myRow, 2).Value - Cells(myRow, 1).Value If myCount 0 Then Rows(myRow).Copy Rows(myRow + 1).Resize(myCount).Insert With Cells(myRow + 1, 1).Resize(myCount) .FormulaR1C1 = "=R[-1]C+1" .Value = .Value End With End If End If Next myRow Columns(2).Delete End Sub "mg_sv_r" wrote in message ... Hi I have a bit of a nightmare task on to carry out on large worksheets that I am hoping can be automated in some way because manually this will take far too long The worksheets basically look like this... -------A------- -------B------- ------C------- -------D------- 1 ---61236----- ---61239----- - a text val -- - a text val -- 2 ---61531----- ---61531----- - B text val -- - B text val -- The number in column A and the number in column B represent a range for which the values in the other columns apply. (So if a given number is between A and B then C and D are true). What I need to do is change this sheet so that instead of giving the number range in columns A and B, it gives each number individually. (Instead of A saying 61236 and B saying 61245 I need A to list 61236, 61237, 61238 etc with all other values applicable copied down the sheet.) The example abopve would end up looking something like this.. -------A------- ------B------- -------C------- 1 ---61236----- - a text val -- - a text val -- 2 ---61237----- - a text val -- - a text val -- 3 ---61238----- - a text val -- - a text val -- 4 ---61239----- - a text val -- - a text val -- 5 ---61531----- - B text val -- - B text val -- Is this possible or would it require some advanced programming or similar? Thanks in advance for any help. Regards John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to insert rows after each row of data (800 rows)? | Excel Discussion (Misc queries) | |||
| Insert rows based on specific value | Excel Worksheet Functions | |||
| How to insert rows after each row of data (800 rows)? | Excel Discussion (Misc queries) | |||
| Insert New Rows based on COUNTA() | New Users to Excel | |||
| Insert a number of rows based on a value in a cell on active row | Excel Worksheet Functions | |||
Linear Mode
