|
adding row when user reaches the last available empty row
I have created a spreadsheet that is divided up into 3 sections... It looks
something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
Also... these rows are formatted with borders, and with a certain color,
certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
A couple of things possible here... most of the solutions depend on how
comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
I'm not that great with vba... I dont know much at all... I haven't done a
whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
Based on that here is my suggestion. Lets stay away from the VBA as much as
possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
I actually cannot change that. It has to remain in the same format
unfortunately. If the solution to this problem does involve some vb, I'm more than willing to give it a try... At this point, I'm willing to try anything to get it resolved. Thanks again for your quick reply! "Jim Thomlinson" wrote: Based on that here is my suggestion. Lets stay away from the VBA as much as possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
Here is the basics of what you want. It looks at the active cell and move up
or down to find the firs non blank row. It inserts a row beneth that and copies the formats to that new row. This code should be pasted directly into the sheet that contains the addresses. I am assuming that there are no formulas in this sheet. This procedure can be called from a button that we will add if that is what you need. HTH "Stacie Fugate" wrote: I actually cannot change that. It has to remain in the same format unfortunately. If the solution to this problem does involve some vb, I'm more than willing to give it a try... At this point, I'm willing to try anything to get it resolved. Thanks again for your quick reply! "Jim Thomlinson" wrote: Based on that here is my suggestion. Lets stay away from the VBA as much as possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
The procedure sounds good. There are no formulas on this sheet, that is
correct. Is there a way I can get the spreadsheet to run this procedure without having to click a button? Can it run automatically when it realizes that one of the blank rows has been filled with data? Does it have to be tied to a button? "Jim Thomlinson" wrote: Here is the basics of what you want. It looks at the active cell and move up or down to find the firs non blank row. It inserts a row beneth that and copies the formats to that new row. This code should be pasted directly into the sheet that contains the addresses. I am assuming that there are no formulas in this sheet. This procedure can be called from a button that we will add if that is what you need. HTH "Stacie Fugate" wrote: I actually cannot change that. It has to remain in the same format unfortunately. If the solution to this problem does involve some vb, I'm more than willing to give it a try... At this point, I'm willing to try anything to get it resolved. Thanks again for your quick reply! "Jim Thomlinson" wrote: Based on that here is my suggestion. Lets stay away from the VBA as much as possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
That is a lot more ugly as it will have to be tied to a change event which
recognizes that someone wants to add a new line (not modify an existing line or delete a line). Definitely tricky and requiring a lot of work and debugging. Stick with the button and you are home free. HTH "Stacie Fugate" wrote: The procedure sounds good. There are no formulas on this sheet, that is correct. Is there a way I can get the spreadsheet to run this procedure without having to click a button? Can it run automatically when it realizes that one of the blank rows has been filled with data? Does it have to be tied to a button? "Jim Thomlinson" wrote: Here is the basics of what you want. It looks at the active cell and move up or down to find the firs non blank row. It inserts a row beneth that and copies the formats to that new row. This code should be pasted directly into the sheet that contains the addresses. I am assuming that there are no formulas in this sheet. This procedure can be called from a button that we will add if that is what you need. HTH "Stacie Fugate" wrote: I actually cannot change that. It has to remain in the same format unfortunately. If the solution to this problem does involve some vb, I'm more than willing to give it a try... At this point, I'm willing to try anything to get it resolved. Thanks again for your quick reply! "Jim Thomlinson" wrote: Based on that here is my suggestion. Lets stay away from the VBA as much as possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
Here is the code... I forgot to attch it to the lase E-mail... :)
Private Sub AddRow() Dim rngAdd As Range Set rngAdd = ActiveCell If rngAdd.Value < Empty Then Set rngAdd = rngAdd.End(xlDown).Offset Else Set rngAdd = rngAdd.End(xlUp).Offset End If rngAdd.Offset(1, 0).EntireRow.Insert rngAdd.EntireRow.Copy Set rngAdd = rngAdd.Offset(1, 0) rngAdd.PasteSpecial xlPasteFormats Application.CutCopyMode = False End Sub "Stacie Fugate" wrote: The procedure sounds good. There are no formulas on this sheet, that is correct. Is there a way I can get the spreadsheet to run this procedure without having to click a button? Can it run automatically when it realizes that one of the blank rows has been filled with data? Does it have to be tied to a button? "Jim Thomlinson" wrote: Here is the basics of what you want. It looks at the active cell and move up or down to find the firs non blank row. It inserts a row beneth that and copies the formats to that new row. This code should be pasted directly into the sheet that contains the addresses. I am assuming that there are no formulas in this sheet. This procedure can be called from a button that we will add if that is what you need. HTH "Stacie Fugate" wrote: I actually cannot change that. It has to remain in the same format unfortunately. If the solution to this problem does involve some vb, I'm more than willing to give it a try... At this point, I'm willing to try anything to get it resolved. Thanks again for your quick reply! "Jim Thomlinson" wrote: Based on that here is my suggestion. Lets stay away from the VBA as much as possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
I'll give it a try and see if it works, then let ya know. Thanks for all
your help! "Jim Thomlinson" wrote: Here is the code... I forgot to attch it to the lase E-mail... :) Private Sub AddRow() Dim rngAdd As Range Set rngAdd = ActiveCell If rngAdd.Value < Empty Then Set rngAdd = rngAdd.End(xlDown).Offset Else Set rngAdd = rngAdd.End(xlUp).Offset End If rngAdd.Offset(1, 0).EntireRow.Insert rngAdd.EntireRow.Copy Set rngAdd = rngAdd.Offset(1, 0) rngAdd.PasteSpecial xlPasteFormats Application.CutCopyMode = False End Sub "Stacie Fugate" wrote: The procedure sounds good. There are no formulas on this sheet, that is correct. Is there a way I can get the spreadsheet to run this procedure without having to click a button? Can it run automatically when it realizes that one of the blank rows has been filled with data? Does it have to be tied to a button? "Jim Thomlinson" wrote: Here is the basics of what you want. It looks at the active cell and move up or down to find the firs non blank row. It inserts a row beneth that and copies the formats to that new row. This code should be pasted directly into the sheet that contains the addresses. I am assuming that there are no formulas in this sheet. This procedure can be called from a button that we will add if that is what you need. HTH "Stacie Fugate" wrote: I actually cannot change that. It has to remain in the same format unfortunately. If the solution to this problem does involve some vb, I'm more than willing to give it a try... At this point, I'm willing to try anything to get it resolved. Thanks again for your quick reply! "Jim Thomlinson" wrote: Based on that here is my suggestion. Lets stay away from the VBA as much as possible and stick with native Excel functionallity. Question on is can we either split this one sheet up into 3 seperate sheets one for each customer type or add in a column that indicates the customer type so that we can get rid of the headings from each section of the sheet. This will make it relatively easy to do... "Stacie Fugate" wrote: I'm not that great with vba... I dont know much at all... I haven't done a whole lot, but I've created macros and tweaked them some and learned how the vba works according to what I know I wanted the macro to do. I've also done a little bit in Access, but I by no means use it regularly, but can try to deciper some stuff. I hope that helps. "Jim Thomlinson" wrote: A couple of things possible here... most of the solutions depend on how comfortable you are with VBA. If you are not familiar with VBA then there are some other solutions to explore... Let me know how you feel about VBA... "Stacie Fugate" wrote: Also... these rows are formatted with borders, and with a certain color, certain date formatting involved in a few columns, etc... How can I keep adding rows with the same formatting? "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
1) Why do Linus and Lucy have different addresses?
2) Sorry for (1) 3) How about having three 'Add' commandbuttons in column D, to the right of the three sections. These would find the row where the blank line is to be inserted and do the insert. Add #1, #2, and #3 in column D on the CUST rows (under the buttons) to mark the end of the previous section. Then Buttons 1 and 2 would be like Private Sub CommandButton1_Click() Dim irow& Cells(1, 1).Select ' select something on sheet irow = Columns(4).Find("#2").Row Rows(irow).Insert Cells(irow, 1).Select End Sub and Button 3 would be Private Sub CommandButton3_Click() Dim irow& Cells(1, 1).Select ' select something on sheet irow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row Cells(irow + 1, 1).Select End Sub This assumes no blank lines, but you could add code to check for and add/delete blank lines. Stacie wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- |
adding row when user reaches the last available empty row
Okay... I've tried both solutions, but can't get either to really work how I
want it to... Let me try to rephrase my new idea (since we've determined using a button will be easiest). This is what my spreadsheet looks like: NEW WELLS (Add Row Button Next to Section Title) Smith 1-1 Panola County Texas 1/25/05 J. Brown Davis 1-2 Noble County Texas 2/1/05 B. Smith EXPLORATORY WELLS (Add Row Button Next to Section Title) Smith 1-1 Panola County Texas 1/25/05 J. Brown Davis 1-2 Noble County Texas 2/1/05 B. Smith UPCOMING NOTABLES (Add Row Button Next to Section Title) Smith 1-1 Panola County Texas 1/25/05 J. Brown Davis 1-2 Noble County Texas 2/1/05 B. Smith What would the code behind each different button be to add a row at the bottom of the corresponding section, of course, copying the formatting of borders, background color, font color, etc... just not the values. Thanks Again to both of you for your help! Stacie "Dave D-C" wrote: 1) Why do Linus and Lucy have different addresses? 2) Sorry for (1) 3) How about having three 'Add' commandbuttons in column D, to the right of the three sections. These would find the row where the blank line is to be inserted and do the insert. Add #1, #2, and #3 in column D on the CUST rows (under the buttons) to mark the end of the previous section. Then Buttons 1 and 2 would be like Private Sub CommandButton1_Click() Dim irow& Cells(1, 1).Select ' select something on sheet irow = Columns(4).Find("#2").Row Rows(irow).Insert Cells(irow, 1).Select End Sub and Button 3 would be Private Sub CommandButton3_Click() Dim irow& Cells(1, 1).Select ' select something on sheet irow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row Cells(irow + 1, 1).Select End Sub This assumes no blank lines, but you could add code to check for and add/delete blank lines. Stacie wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- |
adding row when user reaches the last available empty row
I'm glad your enjoying the post so much..hehe... I'm actually really enjoying
the project and making an otherwise difficult spreadsheet very clean and automated. I certainly appreciate your help. I will try out your suggestions tomorrow and let you know how it finally turns out... Again, thanks so much for your assistance, you've been a tremendous help! Stacie "Dave D-C" wrote: I've sure enjoyed your post -- I've wanted something like this for a long time: Two things: 1) to insert a row on demand: Add a button on the last row. 1a) to insert a row before the buttonrow: Private Sub CommandButton1_Click() ' insert row before button Dim iRow& iRow = CommandButton1.TopLeftCell.Row MsgBox iRow ' delete this Cells(iRow, 1).Select Rows(iRow).Insert End Sub 1b) to insert a row after the buttonrow: Private Sub CommandButton2_Click() ' insert row after button Dim iRow& iRow = CommandButton2.TopLeftCell.Row MsgBox iRow ' delete this Cells(iRow + 1, 1).Select ' insert before; then copy; then clear Rows(iRow).Insert Rows(iRow + 1).Copy Rows(iRow) Rows(iRow + 1).Clear End Sub But equally as great is the following 'totals' formula: [e.g. in a4] =Sum(a1:Offset(a4,-1,0)) This has the advantage that the range will increase when a row is inserted just before row 4. =Sum(a1:a3) will not do this. I sure this is not new, but I am thrilled! Stacie wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- |
adding row when user reaches the last available empty row
I've sure enjoyed your post --
I've wanted something like this for a long time: Two things: 1) to insert a row on demand: Add a button on the last row. 1a) to insert a row before the buttonrow: Private Sub CommandButton1_Click() ' insert row before button Dim iRow& iRow = CommandButton1.TopLeftCell.Row MsgBox iRow ' delete this Cells(iRow, 1).Select Rows(iRow).Insert End Sub 1b) to insert a row after the buttonrow: Private Sub CommandButton2_Click() ' insert row after button Dim iRow& iRow = CommandButton2.TopLeftCell.Row MsgBox iRow ' delete this Cells(iRow + 1, 1).Select ' insert before; then copy; then clear Rows(iRow).Insert Rows(iRow + 1).Copy Rows(iRow) Rows(iRow + 1).Clear End Sub But equally as great is the following 'totals' formula: [e.g. in a4] =Sum(a1:Offset(a4,-1,0)) This has the advantage that the range will increase when a row is inserted just before row 4. =Sum(a1:a3) will not do this. I sure this is not new, but I am thrilled! Stacie wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- |
adding row when user reaches the last available empty row
Works like a charm... BUT.. now how do I get it to keep the borders just like
the line above it? I used the code to insert a row above the button... It currently keeps the same row shading (yellow), but I need it to keep the same border (which is just the plain black border around each cell). I tried the code below, however, it kinda goofs up and gives me 2 rows, and the top row keeps the borders, and the second row is just yellow?!?!?! What am I missing here? Private Sub AddRow_Section1_Click() ' insert row before button Dim iRow& iRow = AddRow_Section1.TopLeftCell.Row Cells(iRow, 1).Select Rows(iRow).Insert Selection.EntireRow.Insert ' select the range for borders MyRow = ActiveCell.Row MyRan = "A" & MyRow & ":" & "P" & MyRow Range(MyRan).Select ' Now do borders Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'With Selection.Borders(xlInsideHorizontal) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic 'End With MyRan = "A" & MyRow Range(MyRan).Select End Sub "Stacie Fugate" wrote: I'm glad your enjoying the post so much..hehe... I'm actually really enjoying the project and making an otherwise difficult spreadsheet very clean and automated. I certainly appreciate your help. I will try out your suggestions tomorrow and let you know how it finally turns out... Again, thanks so much for your assistance, you've been a tremendous help! Stacie "Dave D-C" wrote: I've sure enjoyed your post -- I've wanted something like this for a long time: Two things: 1) to insert a row on demand: Add a button on the last row. 1a) to insert a row before the buttonrow: Private Sub CommandButton1_Click() ' insert row before button Dim iRow& iRow = CommandButton1.TopLeftCell.Row MsgBox iRow ' delete this Cells(iRow, 1).Select Rows(iRow).Insert End Sub 1b) to insert a row after the buttonrow: Private Sub CommandButton2_Click() ' insert row after button Dim iRow& iRow = CommandButton2.TopLeftCell.Row MsgBox iRow ' delete this Cells(iRow + 1, 1).Select ' insert before; then copy; then clear Rows(iRow).Insert Rows(iRow + 1).Copy Rows(iRow) Rows(iRow + 1).Clear End Sub But equally as great is the following 'totals' formula: [e.g. in a4] =Sum(a1:Offset(a4,-1,0)) This has the advantage that the range will increase when a row is inserted just before row 4. =Sum(a1:a3) will not do this. I sure this is not new, but I am thrilled! Stacie wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- |
adding row when user reaches the last available empty row
One more question for you... I've created a button at the top of the form
that says "Move Row to Tracking Tab". When this button is clicked, I want it to move the current row (where their cursor is), to the Tracking Tab, and add it either at the top of the spreadsheet (under the header row) or to the very bottom. The first cell that contains data on the Tracking Tab is A4. Everything above that is the header. I've tried the following code, but cant get it to work out right. It's giving me fits with the line "Range("A4").Select", and I'm not really sure why. Here is the code I used: Private Sub MoveRow_Click() ActiveCell.EntireRow.Copy Worksheets("Tracking").Activate 'the other sheet Range("A4").Select ActiveCell.Insert Shift:=xlShiftDown Worksheets("Western").Activate 'back to the original ActiveCell.EntireRow.Delete Shift:=xlShiftUp End Sub "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
Nevermind on this last issue about Moving Rows to Tracking Tab, I got that
one figured out. "Stacie Fugate" wrote: One more question for you... I've created a button at the top of the form that says "Move Row to Tracking Tab". When this button is clicked, I want it to move the current row (where their cursor is), to the Tracking Tab, and add it either at the top of the spreadsheet (under the header row) or to the very bottom. The first cell that contains data on the Tracking Tab is A4. Everything above that is the header. I've tried the following code, but cant get it to work out right. It's giving me fits with the line "Range("A4").Select", and I'm not really sure why. Here is the code I used: Private Sub MoveRow_Click() ActiveCell.EntireRow.Copy Worksheets("Tracking").Activate 'the other sheet Range("A4").Select ActiveCell.Insert Shift:=xlShiftDown Worksheets("Western").Activate 'back to the original ActiveCell.EntireRow.Delete Shift:=xlShiftUp End Sub "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
I've got a spreadsheet where I've created a button and when it's clicked it
will move the current row to the appropriate tab and insert it there instead of replacing it. It also deletes the old row. Now, for my question regarding this: My spreadsheet is divided up into 3 sections. For the sake of simplicity, I'll just call them Well Type 1, Well Type 2 and Well Type 3. They are laid out like this: WELL TYPE 1 INFORMATION Smith #1 County ST 1/13/05 Jones #2 County ST 1/14/05 WELL TYPE 2 INFORMATION Brown #3 County ST 1/16/05 WELL TYPE 3 INFORMATION Haynes #6 County ST 1/17/05 Currently, under the last row of information under each section, there is an add button that when clicked will add a new row (formatted as necessary with borders and background cell color) under the specified section. My workbook has 2 tabs, Western, and Tracking. Both of these tabs look exactly the same (just like shown above), the only difference is the data that exists in each section. My question is this: now that I've got it set up to move the rows and delete the old row, and it's working fine, how do I make excel move it to the right section? If they click on a cell that falls under Well Type 2, and hit the Move Row to Tracking Tab button, how can I make excel know to move it under the Well Type 2 section of the Tracking Tab? How can it look for a section title and know to insert it under that section? Also, if it inserts the row, will it keep the appropriate formatting??? I'm not all that great with vba, but am learning quickly. Any help is greatly appreciated! Stacie "Stacie Fugate" wrote: I have created a spreadsheet that is divided up into 3 sections... It looks something like this: (I'll use customers as fake data to help make it easier to understand) CUSTOMER TYPE 1 INFORMATION Charlie Brown 1234 Anywhere Drive Nowhere, ST Sally Brown 5678 Nowhere Drive Anywhere, ST Linus VanPelt 9101 Somewhere Drive Somewhere, ST CUSTOMER TYPE 2 INFORMATION Lucy VanPelt 4321 Anywhere Drive Nowhere, ST Peppermint Patty 9876 Nowhere Drive Anywhere, ST CUSTOMER TYPE 3 INFORMATION Snoopy 5678 Somewhere Drive Somewhere, ST Woodstock 4567 Nowhere Drive Nowhere, ST Schroeder 2345 Anywhere Drive Anywhere, ST Marcy 9635 Overthere Street Overhere, ST Now what I have is a row or 2 of empty rows available for when users need to add new data. However, they add new data frequently, and will be needing to add rows often. I dont want to just add a bunch of available empty rows between the 3 different sets of information, because I do not want it to print with a bunch of empty lines. Also, I dont want to add a bunch of empty rows and just hide them, because users may not know that there are hidden rows available, and some do not know how to unhide rows. My 2 solutions to this problem a 1.) I need a way to prompt someone to ask them if they need a new row added under "whichever section they need" when there is no longer an available row. The kicker is that I need to keep on adding rows to the 3 different sections.... so anytime the number of available rows runs out for the selection they need, then it asks them if they would like to insert a new row under section "Customers 2" (prompted because the last available row in Customers 2 has just been used). A problem I see with this is that if someone uses up the last line, and are finished in the worksheet, and they get the prompt asking them if they need to add a new row under Customers 2, they will naturally say "no' because they are done. But when the next person enters that spreadsheet, they might need a new row under "Customers 2", and they would need the prompt so that they could enter a row... Make sense? 2.) My other solution would be to create a macro that would always allow for about 3-5 lines of empty rows, available for use under each section. If someone is typing in one of the lines, as soon as they begin typing in that row, excel recognizes that it will have one less available row, so it will automatically add a new row to compensate for the other row being used. Any help you can provide will be EXTREMELY useful! Thanks! Stacie |
adding row when user reaches the last available empty row
Single-stepping (F8) thru your code immediately shows
Rows(iRow).Insert Selection.EntireRow.Insert is what is giving you two inserted rows. I'm learning a lot -- I've added comments to your code. Private Sub AddRow_Section1_Click() ' insert row before button Dim iRow&, MyRow&, MyRan$ iRow = Addrow_Section1.TopLeftCell.Row Cells(iRow, 1).Select Rows(iRow).Insert ' Selection.EntireRow.Insert ' delete this ' At this point you can (maybe) copy formats and formulae: Rows(iRow - 1).Copy Rows(iRow).PasteSpecial xlPasteFormats Rows(iRow).PasteSpecial xlPasteFormulas ' Unfortunately, this also pastes values (XL97), ' so you might as well Rows(iRow - 1).Copy Rows(iRow) ' instead of the above 3 lines. ' But you don't want old values: Cells(iRow, 3) = "" ' .Clear clears formats ' There are probably things to change: With Rows(iRow).Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = xlAutomatic End With 'So I think this does it ' ' select the range for borders ' MyRow = ActiveCell.Row ' MyRan = "A" & MyRow & ":" & "P" & MyRow ' Range(MyRan).Select ' ' Now do borders ' Selection.Borders(xlDiagonalDown).LineStyle = xlNone ' Selection.Borders(xlDiagonalUp).LineStyle = xlNone ' With Selection.Borders(xlEdgeLeft) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic ' End With ' With Selection.Borders(xlEdgeTop) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic ' End With ' With Selection.Borders(xlEdgeBottom) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic ' End With ' With Selection.Borders(xlEdgeRight) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic ' End With ' With Selection.Borders(xlInsideVertical) ' .LineStyle = xlContinuous ' .Weight = xlThin ' .ColorIndex = xlAutomatic ' End With ' 'With Selection.Borders(xlInsideHorizontal) ' ' .LineStyle = xlContinuous ' ' .Weight = xlThin ' ' .ColorIndex = xlAutomatic ' 'End With ' MyRan = "A" & MyRow ' Range(MyRan).Select ' I would think better is Rows(iRow).Select ' or Cells(iRow, 1).Select End Sub "Stacie wrote: Works like a charm... BUT.. now how do I get it to keep the borders just like the line above it? I used the code to insert a row above the button... It currently keeps the same row shading (yellow), but I need it to keep the same border (which is just the plain black border around each cell). I tried the code below, however, it kinda goofs up and gives me 2 rows, and the top row keeps the borders, and the second row is just yellow?!?!?! What am I missing here? [code is above] |
| All times are GMT +1. The time now is 05:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com