Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Number of records to add/delete in XL macro

    I have several workbooks into which I paste the values generated by reports or queries from our different business applications. I save the target workbook with the date as part of the filename and then use the most recent version to receive the new source data. The source data will vary in record length, sometimes more rows than the last, and sometimes fewer.

    Each workbook has a range of formulas above the pasted source data that summarize the data. Instead of adding or deleting data at the bottom of the range I insert rows just below the title row of the data in order to ensure that the summary formulas at the top of the sheet always include the entire range of data. That saves the trouble of having to recheck the summary formulas each time new data is received.

    I use a formula to help calculate how many rows to add or delete, using the existing record count and the total of the new source data record count. The calculation appears in the attached workbook in the range C2D6.

    I would like to incorporate the results of that formula into a macro to automate the steps of adding or deleting the right number of rows.
    Attached Files Attached Files
    Last edited by Arcturus16a; 2011-06-16 at 14:28. Reason: Trying to delete Happy Face

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    What you need to use is a Dynamic Range Name. You can do a search here as there are several threads which have discussed it in the past. No need for a macro.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Yes. I see the value of dynamic ranges. But I need to be able to expand or contract the entire collection which includes not only imported data but formulas to the left of that data. In the attachment, you'll see that columns A through H are formulas that assign a value, rank or category to each individual record. The imported data (pasted vaules from a query or report) are placed in columns to the right, starting in column I. So in the attached example, OFFSET would work fine in the summary section above the imported data, but I cannot think of a solution for the ranking/categorizing formulas in the columns A:H. In fact, it is those columns that help sort the results in pivot tables.

    (Formula in col G has been modified but contains something like this: =IF(ISERROR(SEARCH("refused",I24)),IF(AND(SUM(K24: O24)<>0,SUM(P24:S24)<>0),"Both", IF(AND(SUM(K24:O24)=0,SUM(P24:S24)<>0),"MZ", IF(AND(SUM(K24:O24)<>0,SUM(P24:S24)=0),"GL", "No"))),"MZ-Refused") and is a primary Report Filter in a Pivot Table.

    So the whole purpose of my request is to be able to match the record count in number of rows which would include all the formulas in the left columns prior to importing/pasting the query values.

    PS. Thanks for the tip on searching this site for dynamic ranges. I see several things that will be helpful.
    Last edited by Arcturus16a; 2011-06-16 at 18:57.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Your attachment is missing.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    It's there in the first posting. I just tried it.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Arcturus,

    Sorry, I didn't scroll back up.

    From what I can see your application would be enhanced by the use of dynamic ranges and the deleting of all data below row 18.
    A macro could then import the data from I19 and then fill in the formulas in A-H for each row imported using the macro determined last row to fill in the limit on many of the formula. This approach could eliminate most if not all of the user interaction required in the white box. Of course, this is just a quick take from looking at the sample w/o a complete understanding of the models purpose and construction.

    Good Luck.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    If I need to add, say 100 rows, then OFFSET/INDEX/MATCH will be useful in adjusting the formula results at the top of the rows.

    But, the formulas in the columns to the left of the imported data cannot be adjusted or created with OFFSET/INDEX/MATCH. These are formulas which I need to categorize and summarize the imported data.

    I must manually select these formulas and copy/paste them to the left of the newly added data. That is the reason I'm looking for a macro solution; to copy a range of rows and then insert/add them so that when the new data is pasted to overwrite the old (and 100 recently copied) rows of data all of them will have the formulas that appear on A:H.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    There is no need to copy past the formulas from what I can see. You could easily loop a macro to work it's way down the imported rows and insert the formulas
    Ex: A19: =IF(RANK(N19,N$19:N$235,0)<=A$15,1,0)
    Could be represented in a macro statement as Cells(19,1).formular1c1 = "=if(rank(RC14:R" & format(lLastRow) &"C14,0)<=GlTheoRank,1,0)
    Where: GITheoRank is the name given to cell A15
    lLastRow is a name given to the last row of the data as determined earlier in the macro.
    You write similiar code for each of the cells B:H then incorporate the whole thing in a loop and no more manual minipulation.
    The VBA code can also be made to import the data in the first place.

    Yes this is a lot of work to get going. However, the payoff is you only need to do it once and you eliminate errors and processing time for ever more...you do the math.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    OK. But then there's still the issue of adjusting rages in each of the pivot tables that refer to the data. And this is for just one of the workbooks in which I use the same method for data that needs to be categorized (using formulas not in the data) and then summarized with pivot tables using these new categories.

    Recreating several columns of formulas for each new record using a macro in the way you suggest would require me to inspect all code any time I am asked to create a new column/field to categorize/summarize data. That would move me even further away from passing this workbook on to co-workers since the column references in the macro would not recognize insertion of new column/field in the workbook.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is way with formulas and dyanamic ranges:

    Define the names (formulas - name manager - New
    Name: IDHeader
    refers to:=Data!$I$18
    [ok][New]
    Name: DataRange
    refers to:=OFFSET(INDIRECT("a1"),ROW(IDHeader),0,Data!$D$ 2,COLUMN(IDHeader)+9)
    [ok][close]

    In D2: =COUNTA(OFFSET(IDHeader,1,0,ROWS(INDIRECT("a:a"))-ROW(IDHeader),1))

    In I2: =COUNTIF(INDEX(DataRange,0,COLUMN(IDHeader)-1),H2)
    In J2: =SUMIF(INDEX(DataRange,0,COLUMN(IDHeader)-1),$H2,INDEX(DataRange,0,COLUMN()))
    Copy J2 to K2:R2
    Copy I2:R2 to I4:R5 and to I8:R8

    I12: =COUNTA(INDEX(DataRange,0,COLUMN()))
    J12: =SUM(INDEX(DataRange,0,COLUMN()))
    Copy J12 to K12:R12

    I16:=SUMPRODUCT(1/COUNTIF(INDEX(DataRange,0,COLUMN()),INDEX(DataRang e,0,COLUMN())))
    L17: =COUNTIF(INDEX(DataRange,0,COLUMN()),">0")

    A17: =SUM(INDEX(DataRange,0,COLUMN()))
    Copy A17 to B17,C17 and E17

    A19: =IF(RANK(N19,INDEX(DataRange,0,COLUMN(N19)),0)<=A$ 15,1,0)
    B19: =IF(RANK(G19,INDEX(DataRange,0,COLUMN(G19)),0)<=B$ 15,1,0)
    C19: =IF(RANK(D19,INDEX(DataRange,0,COLUMN(D19)),0)<=$C $15,1,0)
    E19: =IF(RANK(F19,INDEX(DataRange,0,COLUMN(F19)),0)<=$E $15,1,0)
    Autofill/Copy A19:E19 down the rows...

    When you add new data is pasted into Columns I:R, the range will be adjusted for the count of items in Column I....

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Steve. I'll try this out on next week's report (going home for weekend now).

    I guess I probably oversimplified the task in my head. I thought it might just be easier to copy and paste/insert the rows through a macro 'cause thats the easy way manually.

    I had envisioned something like this:
    Accept/capture input from cells in range D26

    If D6 is Positive Call Insert Rows
    If D6 is Negative Call Delete Rows

    Insert Rows (D6 is Positive)
    Go to a cell in col A (get result from D4)
    Then while selecting all rows in between
    Go to another cell in col A (get result from D5)

    Copy all of the selected rows
    Paste/Insert all of those rows

    CTRL+ Home

    Delete Rows (D6 is Negative)
    Go to a cell in col A (get result from D4)
    Then while selecting all rows in between
    Go to another cell in col A (get result from D5)

    Delete all of the selected rows
    CTRL + Home

    Have a good weekend to you both.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps I don't understand. If you are going to use a macro, I don't see the need for all the user input / counting etc, the code could do this at runtime.

    I used the range, so there was no need for the code. just paste the values into I:R and the formulas adjust themselves. The formulas will work even if you use some code to insert the range and copy the formulas in A:G down the column...

    If you want code, you will have to detail, what you want the code to all do...

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    When doing this all manually, the shortest route is to copy and paste in the middle of all the data and then paste new results (now an identical record count) over the existing data. In that way, print and pivot table ranges are automatically adjusted, formulas in rows at the top are adjusted, all formulas left of the imported data are properly copied and all formatting, including for the newly added data are set. If the existing record count is greater than the new record count, the manually entered number in D3 results in a negative number in which case the user deletes the selected records.

    The user will only need to have a count of the records to be imported; that goes in D3. I was hoping for a macro that could read the contents of the cells in D2.D6 and then delete (if D6 is negative) or copy n rows (starting at row 20 and selecting all rows to row 238) and then paste+insert those rows. The starting row, cell D4, may change from one workbook to the next, but I could modify that once when I insert the module. The steps I outlined in post #11 are pretty much whta I think would appear in a macro.

    I'm looking to you for a macro that I can add to this workbook and four others that are similar in that they use input from our various business apps and I have added foumulas to the left that help categorize and then summarize in an assortment of pivot tables.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    So you are not looking for adjustable formulas that are dynamic based on the dataset? You are looking for a macro that deletes the current rows in the target dataset (just keeping the first one and last for the formulas), and then inserts the new rows of data and autofills the columns of formulas down the rows to the left of the data?

    What does the importing file look like and how will the user select it for input?
    Steve

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Not adjustable formulas, but a macro that accepts the input of the value of cell D6 and then inserts/deletes rows based on the result. Adjustable formulas with the AutoFill steps you showed me earlier work fine, but
    I've this same basic pattern in several othe worksheets, where I bring in data, add some nested IF statement to assign some category and then use thore results in a pivot table. And if I have a Camera range assigned to print the indert/delete rows within already existing data will automatically adjust the range, and I don't have to reapply formats like shading, fonts or row heights.

    The manual steps are shown in the white text box on the attached worksheet. Please give those manual steps a try. There's not even a need to switch to the data to be copied and pasted. The user will do that. If I could just get a macro to do that much in this workbook, I could add the module to all the other workbooks that work similarly.

    In this case, less is more. If, for example, I ask for this new macro to be designed so that it goes out to a network folder to retrieve a certain file, then any time I intend to place a copy of this macro in a new workbook will require more debugging. But business apps that create the data assign different filenames each time the query or report are run. Again, that would be more than necessary. I'm just hoping for a sort of generic solution that I could then apply to all similarly designed worksheets.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •