Inserting Rows based on Variable

[tbakbradley]

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.

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").Select

Thank you so much for anyone that is willing to take the time to look at this.

 

[tbakbradley]

The Code to insert the rows has worked wonderfully on many of the Tools I had to update. However, I’m running into a problem on one tool, because I have two Worksheets that both have equations that point to the other worksheet, which is causing a problem.

The Spreadsheet in question has two Worksheets that require Deletion or Insertion of Rows. “TLS Inquiry” and “Locations”. Both are Defaulted to 10 (Row 4 through Row 13). If User enters less than 10, then the Macro will delete Rows. If the User enters more than 10, then the code from this thread is used to insert rows with copied data/equations and correctly increase the Location Numbers in Column A. All that works incredibly well.

Here is the problem: Because my “TLS Inquiry” Worksheet has a series of Columns and Rows that include equations that point to the “Locations” Worksheet, there is a problem with the References in the cells AFTER Row 13 (Location 10).

The beginning of the equations on the “TLS Inquiry” Worksheet pulls data from the “Locations” Worksheet looks like this: (Locations!O4, Locations!O5….Locations!O13 (Row for 10^th Location that was prebuilt)). Beginning on Row 14, the equation does NOT begin with Locations!O14 like it should. It skips numbers. The skipped numbers are different based on how many locations are entered by the User. I figured out what it’s doing, but not sure why or how to fix it. The Locations!O(Y) for the 14^th Row is dependent on the (X) locations entered by the User. Y=13+((X-10)+1) every time. Example: User enters 15 Locations. Y=13+((15-10)+1) = 19. So, beginning on Row 14, the equation is Locations!O19 opposed to Locations!O14. This is obviously creating incorrect calculations, etc.

So, if 50 locations were entered, then Row 14 Equation would be Y=13+((50-10)+1)= 54 (Locations!O54).

All other equations work fine when inserting Rows. It’s only an issue when it’s pointing to another Worksheet that also had rows Inserted. I believe that is the culprit, but have no idea how to resolve that. I tried having the Macro insert Rows first on the “Locations” Worksheet, then the “TLS Inquiry”, but I received more of the same issues. Because the “Locations” Worksheet also pulls data from the “TLS Inquiry”, there were problems there too when inserting that WS first.

Not sure what to show that could be of extra assistance, but can provide where needed.

 

[tbakbradley]

Here is the portion of Code that Inserts Rows if "locations" is > 10. The first section is on the Worksheet "TLS Inquiry" and the second insert section is on the Worksheet "Locations":

Issue: Columns FX to IG on Worksheet "TLS Inquiry", each row has an equation that points to the "Locations" Worksheet. FX4=Locations!O4.....FX13=Locations!O13. This is the default of 10 Rows (locations) the spreadsheet is built. When Inserting Rows from the code below, everything works perfectly, except for Equations that point to "Locations!" Worksheet. As described in post above, after Row 13, Row 14 the first inserted Row isn't Locations!O14. It skips to be 13+((locations-10)+1). So, for 15 locations entered, that means the 14th Row on Columns FX to IG skips to 13+((15-10)+1)= 13+6 = 19. So, instead of Locations!O14 on the inserted Row, it jumps to Locations!O19. Any ideas?

 'Insert Rows if Locations are > 10
    If locations > 10 Then
    Sheets("TLS Inquiry").Select
    Columns.EntireColumn.Hidden = False
    numbrows = locations - 10
    Range("A14").Resize(numbrows).EntireRow.Insert
    Range("A13").AutoFill Range("A13").Resize(numbrows + 1), xlFillSeries
    Range("B13:IG13").Resize(numbrows + 1).FillDown
    
        
    Sheets("Locations").Visible = True
    Sheets("Locations").Select
    numbrowsB = locations - 10
    Range("A14").Resize(numbrowsB).EntireRow.Insert
    Range("A13").AutoFill Range("A13").Resize(numbrowsB + 1), xlFillSeries
    Range("B13:O13").Resize(numbrows + 1).FillDown
    Range("B4").Select
    Sheets("Locations").Visible = xlVeryHidden
    Sheets("TLS Inquiry").Select
    Range("B4").Select
  End If

 

[tbakbradley]

Looks like I figured it out.

When performing the Insert on the "TLS Inquiry" Worksheet, I removed the

Range("B13:IG13").Resize(numbrows + 1).FillDown

and placed it AFTER the Insertion of Rows for the "Locations" Worksheet. A Quick change and test, and appears to have resolved the issue. So, not copying/pasting all the equations on the "TLS Inquiry" Worksheet until AFTER building the "Locations" Worksheet.