Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
Excel XP
Column A has record numbers: some repeat; some do not. Columns B through K contain other data. Sheet is sorted by Column A. I want to add a blank row before each new number. Example: I have 1 1 2 2 2 3 I want: 1 1 2 2 2 3 I can do this using subtotals to count and then delete the contents of the count rows with helper columns. I can do this by using Countif in a helper column to number each entry and then using Advanced Filter to add each record number one more time to the end of column A. I then add a number greater than the largest result of the Countif (e.g.: 1000) to all the newly added record numbers and re-sort. Then I have to delete the text in the rows with "1000." Is there a simpler way to do this? I would prefer not to use VB as I am totally unfamiliar with it. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
Hi Guy
in my opinion it depends on the size of your data. If you have to do this often, and the data is large i would recommend a small VBA-function. Maybe someone else can help you, with a non-VB approach. Otherwise if you need help with VBA, just ask. Cheers Carlo On Nov 21, 4:03 pm, Guy Lydig wrote: Excel XP Column A has record numbers: some repeat; some do not. Columns B through K contain other data. Sheet is sorted by Column A. I want to add a blank row before each new number. Example: I have 1 1 2 2 2 3 I want: 1 1 2 2 2 3 I can do this using subtotals to count and then delete the contents of the count rows with helper columns. I can do this by using Countif in a helper column to number each entry and then using Advanced Filter to add each record number one more time to the end of column A. I then add a number greater than the largest result of the Countif (e.g.: 1000) to all the newly added record numbers and re-sort. Then I have to delete the text in the rows with "1000." Is there a simpler way to do this? I would prefer not to use VB as I am totally unfamiliar with it. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
In the meantime, would you mind giving me the VBA solution?
I appreciate your assistance. "carlo" wrote: Hi Guy in my opinion it depends on the size of your data. If you have to do this often, and the data is large i would recommend a small VBA-function. Maybe someone else can help you, with a non-VB approach. Otherwise if you need help with VBA, just ask. Cheers Carlo On Nov 21, 4:03 pm, Guy Lydig wrote: Excel XP Column A has record numbers: some repeat; some do not. Columns B through K contain other data. Sheet is sorted by Column A. I want to add a blank row before each new number. Example: I have 1 1 2 2 2 3 I want: 1 1 2 2 2 3 I can do this using subtotals to count and then delete the contents of the count rows with helper columns. I can do this by using Countif in a helper column to number each entry and then using Advanced Filter to add each record number one more time to the end of column A. I then add a number greater than the largest result of the Countif (e.g.: 1000) to all the newly added record numbers and re-sort. Then I have to delete the text in the rows with "1000." Is there a simpler way to do this? I would prefer not to use VB as I am totally unfamiliar with it. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
Hy Guy
here's a small VBA Sub: '---------------------------------------------------------------------------- Sub ins_rows() Dim var_before As Variant var_before = Cells(1, 1).Value For i = 1 To 65536 If Cells(i, 1).Value = "" Then Exit For End If If var_before < Cells(i, 1).Value Then var_before = Cells(i, 1).Value Rows(i).Insert Shift:=xlDown i = i + 1 End If Next i End Sub '---------------------------------------------------------------------------- i think you know how to put it in the VBA-editor, but if not, just ask and i will try to explain it to you. Also if you want further explanation to the code. hth Carlo On Nov 22, 12:11 am, Guy Lydig wrote: In the meantime, would you mind giving me the VBA solution? I appreciate your assistance. "carlo" wrote: Hi Guy in my opinion it depends on the size of your data. If you have to do this often, and the data is large i would recommend a small VBA-function. Maybe someone else can help you, with a non-VB approach. Otherwise if you need help with VBA, just ask. Cheers Carlo On Nov 21, 4:03 pm, Guy Lydig wrote: Excel XP Column A has record numbers: some repeat; some do not. Columns B through K contain other data. Sheet is sorted by Column A. I want to add a blank row before each new number. Example: I have 1 1 2 2 2 3 I want: 1 1 2 2 2 3 I can do this using subtotals to count and then delete the contents of the count rows with helper columns. I can do this by using Countif in a helper column to number each entry and then using Advanced Filter to add each record number one more time to the end of column A. I then add a number greater than the largest result of the Countif (e.g.: 1000) to all the newly added record numbers and re-sort. Then I have to delete the text in the rows with "1000." Is there a simpler way to do this? I would prefer not to use VB as I am totally unfamiliar with it. Thanks- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
It works beautifully but...
all I did was copy and paste into a VBA window and then click Run. 1) How do I save this so I can run it in any workbook I open? 2) How do I create a keyboard shortcut or button on a toolbar to run it? 3) Do you mind briefly explaining exactly what this code does (besides solving my problem!)? Where it says OK I do not need an explanation. Sub ins_rows() OK Dim var_before As Variant OK var_before = Cells(1, 1).Value What is Cells(1, 1).Value? For i = 1 To 65536 OK If Cells(i, 1).Value = "" Then I'm not sure what Cells(i, 1).Value is. Exit For OK End If OK If var_before < Cells(i, 1).Value Then OK var_before = Cells(i, 1).Value Huh? If the variant is not = then it is =? Rows(i).Insert Shift:=xlDown ?? i = i + 1 OK End If OK Next i OK End Sub OK Any help will be appreciated. I said I didn't know VBA.... : ( Happy Thanksgiving! "carlo" wrote: Hy Guy here's a small VBA Sub: '---------------------------------------------------------------------------- Sub ins_rows() Dim var_before As Variant var_before = Cells(1, 1).Value For i = 1 To 65536 If Cells(i, 1).Value = "" Then Exit For End If If var_before < Cells(i, 1).Value Then var_before = Cells(i, 1).Value Rows(i).Insert Shift:=xlDown i = i + 1 End If Next i End Sub '---------------------------------------------------------------------------- i think you know how to put it in the VBA-editor, but if not, just ask and i will try to explain it to you. Also if you want further explanation to the code. hth Carlo On Nov 22, 12:11 am, Guy Lydig wrote: In the meantime, would you mind giving me the VBA solution? I appreciate your assistance. "carlo" wrote: Hi Guy in my opinion it depends on the size of your data. If you have to do this often, and the data is large i would recommend a small VBA-function. Maybe someone else can help you, with a non-VB approach. Otherwise if you need help with VBA, just ask. Cheers Carlo On Nov 21, 4:03 pm, Guy Lydig wrote: Excel XP Column A has record numbers: some repeat; some do not. Columns B through K contain other data. Sheet is sorted by Column A. I want to add a blank row before each new number. Example: I have 1 1 2 2 2 3 I want: 1 1 2 2 2 3 I can do this using subtotals to count and then delete the contents of the count rows with helper columns. I can do this by using Countif in a helper column to number each entry and then using Advanced Filter to add each record number one more time to the end of column A. I then add a number greater than the largest result of the Countif (e.g.: 1000) to all the newly added record numbers and re-sort. Then I have to delete the text in the rows with "1000." Is there a simpler way to do this? I would prefer not to use VB as I am totally unfamiliar with it. Thanks- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
Hi Guy
If you want it to work in all your workbooks, you better change it to an add-in. I never did that though, but at least i can try: For that, open a new workbook hit alt+F11 Insert - Module. (Change the name of the Module in the properties window, if you want) Paste the Macro in this module Open the Worksheet in the VBA-editor (if you don't see it press Ctrl+R) Insert following code: '------------------------------------------------------------------------------------------------- Private Sub Workbook_AddinInstall() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCutomMenu As CommandBarControl On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("My Tools").Delete On Error GoTo 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar") For Each mb In Application.CommandBars(1).Controls If mb.ID = 30010 Then iHelpMenu = mb.Index Exit For End If Next mb Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) cbcCutomMenu.Caption = "My Tools" With cbcCutomMenu.Controls.Add(Type:=msoControlButton) .Caption = "Insert rows in Column A" .OnAction = "ins_rows" End With End Sub Private Sub Workbook_AddinUninstall() On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("My Tools").Delete End Sub '------------------------------------------------------------------------------------------------- close the vba-editor save the excel sheet as .xla (it should automatically be saved to your add-in folder) Close Excel and open a new worksheet. Tools - Add-Ins and you should see your add-in in the list (the name with which you saved it without .xla) hope that helps, please test it and give feedback as it is my first try with add-ins. if you want to have additional infos, check google, there are some great tutorials like that from ozgrid: http://www.ozgrid.com/VBA/excel-add-in-create.htm that takes care of your question 1 and 2. now to question 3: it's a fairly simple code, which checks if the value of this rows cell in column A is like the one stored in var_before. To read it more easily copy the whole code and paste it into the VBA-editor. '--------------------------------------------------------------------------------- Sub ins_rows() Dim var_before As Variant 'set var_before to cell A1 (row 1, column 1) 'Cells(1,1).value means the value of Cell A1 var_before = Cells(1, 1).Value For i = 1 To 65536 'this function will only go as far as the first 'blank cell in column A If Cells(i, 1).Value = "" Then Exit For End If 'if the cell value is not the same as the one 'stored in var_before do following: If var_before < Cells(i, 1).Value Then 'set var_before = cell value so that the next 'comparison will be accurate var_before = Cells(i, 1).Value 'insert 1 row where the row(i) is right now, 'move all the other rows down. Rows(i).Insert Shift:=xlDown 'jump over one line, because we inserted one i = i + 1 End If Next i End Sub '--------------------------------------------------------------------------------- if there are any other questions, just ask Cheers Carlo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding five new rows every 40 rows in a spreadsheet? | Excel Discussion (Misc queries) | |||
Adding Rows offsets to working rows across two worksheets | Setting up and Configuration of Excel | |||
adding ROWS | Excel Discussion (Misc queries) | |||
Adding new rows | Excel Discussion (Misc queries) | |||
adding rows | Excel Discussion (Misc queries) |