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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
It seems to work. I have no idea what I'm doing so that makes me uneasy.
Two lines in the Addininstall code and one in the Adduninstall code are red: Application.CommandBars("Worksheet Menu Bar").Controls("My and Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) and Application.CommandBars("Worksheet Menu Bar").Controls("My It doesn't seem to be affecting anything. Thank you so much for your help. It does what it has to do! Guy "carlo" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
On Nov 22, 3:35 pm, Guy Lydig
wrote: It seems to work. I have no idea what I'm doing so that makes me uneasy. Two lines in the Addininstall code and one in the Adduninstall code are red: Application.CommandBars("Worksheet Menu Bar").Controls("My and Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) and Application.CommandBars("Worksheet Menu Bar").Controls("My It doesn't seem to be affecting anything. Thank you so much for your help. It does what it has to do! Guy "carlo" wrote: 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- Hide quoted text - - Show quoted text - Hi Guy Happy that it works. But I'm not to happy with those redlines you told me! the problem is probably the wordwrap of the discussion group. Try to put the lines together, for example: Application.CommandBars("Worksheet Menu Bar").Controls("My is definitely not finished (you maybe know that problem from formulas :) ) so try to take the line below up, be careful it should be "My Tools" not "MyTools". I didn't want to post a detailled description because i didn't know which parts you would understand. But if you have any questions just go ahead and ask. cheers Carlo |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
You are correct about the red being caused by the wrap. All is well now.
Thanks so much. I cannot ask you for further clarification because I need a course in VBA. Right now I am too ignorant to ask questions. That is why I wanted a non-VBA solution. You are very gracious to expend so much effort on my behalf and I appreciate it. Happy Thanksgiving! "carlo" wrote: On Nov 22, 3:35 pm, Guy Lydig wrote: It seems to work. I have no idea what I'm doing so that makes me uneasy. Two lines in the Addininstall code and one in the Adduninstall code are red: Application.CommandBars("Worksheet Menu Bar").Controls("My and Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) and Application.CommandBars("Worksheet Menu Bar").Controls("My It doesn't seem to be affecting anything. Thank you so much for your help. It does what it has to do! Guy "carlo" wrote: 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- Hide quoted text - - Show quoted text - Hi Guy Happy that it works. But I'm not to happy with those redlines you told me! the problem is probably the wordwrap of the discussion group. Try to put the lines together, for example: Application.CommandBars("Worksheet Menu Bar").Controls("My is definitely not finished (you maybe know that problem from formulas :) ) so try to take the line below up, be careful it should be "My Tools" not "MyTools". I didn't want to post a detailled description because i didn't know which parts you would understand. But if you have any questions just go ahead and ask. cheers Carlo |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding rows
Ok, then everyone is happy (except maybe the turkey ;) )
if you still have an answer, just post it. Happy Thanksgiving to you too Carlo On Nov 22, 4:29 pm, Guy Lydig wrote: You are correct about the red being caused by the wrap. All is well now. Thanks so much. I cannot ask you for further clarification because I need a course in VBA. Right now I am too ignorant to ask questions. That is why I wanted a non-VBA solution. You are very gracious to expend so much effort on my behalf and I appreciate it. Happy Thanksgiving! "carlo" wrote: On Nov 22, 3:35 pm, Guy Lydig wrote: It seems to work. I have no idea what I'm doing so that makes me uneasy. Two lines in the Addininstall code and one in the Adduninstall code are red: Application.CommandBars("Worksheet Menu Bar").Controls("My and Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=iHelpMenu) and Application.CommandBars("Worksheet Menu Bar").Controls("My It doesn't seem to be affecting anything. Thank you so much for your help. It does what it has to do! Guy "carlo" wrote: 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- Hide quoted text - - Show quoted text - Hi Guy Happy that it works. But I'm not to happy with those redlines you told me! the problem is probably the wordwrap of the discussion group. Try to put the lines together, for example: Application.CommandBars("Worksheet Menu Bar").Controls("My is definitely not finished (you maybe know that problem from formulas :) ) so try to take the line below up, be careful it should be "My Tools" not "MyTools". I didn't want to post a detailled description because i didn't know which parts you would understand. But if you have any questions just go ahead and ask. cheers Carlo- Hide quoted text - - Show quoted text - |
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) |