tbakbradley
Board Regular
- Joined
- Sep 24, 2010
- Messages
- 142
I have an Excel Spreadsheet built with 500 rows (Locations) beginning on Row 4 and ending on Row 504. There are Summations under it as well as Summary Tables. The User is able to select the number of Locations they require for the specific job, as long as it's not more than 500. If less than 500, the Macro will Delete the required number of Rows.
I have an old Spreadsheet tool I built that will Insert Rows, copy the row above it (with all the equations) and paste in the new Rows. However, it's VERY slow as I Recorded a Macro for the Code. FYI...I did the same to Delete rows and it was slow too until someone here provided a much simpler method.
I could use the slower method to insert Rows, but the problem I have is with column A which provides a "Location #" for easy sorting, etc. as different organizations will touch it later. Deleting from a completed spreadsheet isn't a problem keeping the "Location #s " correct. Inserting, I'm not sure about.
Here is the Code (Slow) that I have to insert Rows based on the number selected (this is an old Spreadsheet with 59 Rows/Locations already prebuilt. First Row of Data starts on Row 2, and the 59th Location is on Row 60). First....what is a better method to Insert a Row (so I do not mess up calculations at the bottom) and paste the equations from the row above? Second, allow for Column A to have the proper "Location #"? Today, Location # is "Location 1, Location 2, Location 3.......Location 59). Using this slow method, I would insert at Location 59 (Row 60), but it would no longer have the proper Location #s in Column A.
Note: "circuits" = the value the user enters when asked how many locations required.
Thank you so much for anyone that is willing to take the time to look at this.
I have an old Spreadsheet tool I built that will Insert Rows, copy the row above it (with all the equations) and paste in the new Rows. However, it's VERY slow as I Recorded a Macro for the Code. FYI...I did the same to Delete rows and it was slow too until someone here provided a much simpler method.
I could use the slower method to insert Rows, but the problem I have is with column A which provides a "Location #" for easy sorting, etc. as different organizations will touch it later. Deleting from a completed spreadsheet isn't a problem keeping the "Location #s " correct. Inserting, I'm not sure about.
Here is the Code (Slow) that I have to insert Rows based on the number selected (this is an old Spreadsheet with 59 Rows/Locations already prebuilt. First Row of Data starts on Row 2, and the 59th Location is on Row 60). First....what is a better method to Insert a Row (so I do not mess up calculations at the bottom) and paste the equations from the row above? Second, allow for Column A to have the proper "Location #"? Today, Location # is "Location 1, Location 2, Location 3.......Location 59). Using this slow method, I would insert at Location 59 (Row 60), but it would no longer have the proper Location #s in Column A.
Note: "circuits" = the value the user enters when asked how many locations required.
Code:
ActiveSheet.Select Sheets("IoF").Select
NumbRows = circuits - 59
For Counter = 1 To NumbRows
Sheets("IOF").Select
Rows("60:60").Select
Range("C60").Activate
Selection.Insert Shift:=x1Down
Rows("59:59").Select
Range("C59").Activate
Selection.Copy
Rows("60:60").Activate
Range("C60").Activate
ActiveSheet.Paste
Range("C60").Select
Next Counter
End If
Sheets("IOF").Select
Application.CutCopyMode = False
Range("D2").SelectThank you so much for anyone that is willing to take the time to look at this.
