Posts by Brandtrock

  • Last Used Cell

    Re: Last Used Cell



    You will see the problem with this method if you do this:


    Enter some text or numbers in A1:A5
    Skip A6
    Enter some text or numbers in A7:A10


    Run your code.


    Where are you?


    Place the cursor back in A1.
    Now run Roy's code on the same sheet.


    Now where are you?


    Roy's method will work correctly as he is going to the last row, first column using the Cells(Rows.Count,1) code. This will go to the bottom of 2003 and 2007 worksheets, even though they have different numbers of rows.


    Quote from RoyUK

    Why not
    [VBA]


    Sub LastCellAfterUsedInColumn() Cells(Rows.Count,1).End(xlUp).Select End Sub
    [/vba]


    The FIND method listed elsewhere in this thread is also a better solution than the code you posted.


    Hope this helps,

  • Congratulations Will Riley On Passing The 8,000 Mark

    Re: Congratulations Will Riley On Passing The 8,000 Mark


    Given your location, I would guess that this milestone translates loosely to about 4100 euros. In any case, your posts have been valuable to the members here at Oz. In addition to your knowledge of Excel, your expertise with SQL and relational databases is very impressive. Keep up the good work mate.


    Oh, and :drink: :drink: :drink:


    Later buddy,

  • Addin Registry

    Re: Addin Registry


    Regarding changing the extension:


    John Walkenback's Power Utility Pak uses .pup extensions on his files. The main add-in then loads these when a given utility is being used.


    As usual, Dennis has great input for these type of things.


    Regards,

  • Pivot Table That Get Data Dynamically

    Re: Pivot Table That Get Data Dynamically


    Here you go.


    I inserted three dynamic named ranges:


    ContractsPivotSource
    SuppliersPivotSource
    DeptPivotSource


    these will expand and contract based on the data entered in the three data sheets (Contracts, Suppliers, Dept).


    I made three separate pivot tables, one for each set of data. Admittedly, the layout is horrid. You can change the layout to whatever you desire. Be warned however that deleting the pivot table and recreating it will REQUIRE changing the refresh coding - this is documented by comment in the code as well.


    The Contracts and the Suppliers userforms had close buttons so the code to refresh the appropriate pivot table is in the click event of those buttons. The third userform was not as clear as to where to put the refresh code. I will leave that up to you. It will be the same idea as the code for the other two. Just make sure to change the sheet name and the pivot table name.


    Regards,

  • Pivot Table That Get Data Dynamically

    Re: Pivot Table That Get Data Dynamically


    Tara,


    Thanks for attaching the file. Do you want one pivot table encompassing all of the data (hard), one pivot table for each sheet's data (easier), or one table for just the Contracts sheet (easiest)?


    Do you want the refresh to happen when the data entry forms are closed using the click button? (I'm guessing you do)


    It is quite late here now, but I will be happy to work on this in the morning for you.


    Regards,

  • Pivot Table That Get Data Dynamically

    Re: Pivot Table That Get Data Dynamically


    Yes, it's possible.


    The example I posted earlier has all the pieces we need except the userform.


    Do you have a file started? If so, could you attach it (scroll down to manage attachments in the reply window. If not, do you have a layout in mind for the columns and the userform?


    Regards,

  • Pivot Table That Get Data Dynamically

    Re: Pivot Table That Get Data Dynamically


    Tara01:


    I understood what you wanted. My example simply illustrates the creation of the dynamic named range and the programmatic refreshing of the pivot table created from that data.


    I assumed that you are using a userform to enter data, that the data is being stored on a spreadsheet, and that that group of data is then used as your pivot table's pivot source. When you add additional data to the group of data you want to see an updated pivot table.


    1. Are you creating the userform through VBA?
    2. Do you want the data on the same sheet as the pivot table?
    3. When do you want the pivot table to "update"?
    [INDENT]After each new line of data?[/INDENT]
    [INDENT]After all new data is entered?[/INDENT]


    Regards,

  • Importing More Than 65000 Records Into One Worksheet

    Re: Importing More Than 65000 Records Into One Worksheet


    Quote from nat_m

    ...How can I import data which is either more than 65000 records long or it will sum up to be more than 65000 when imported? I need all my data to be on one worksheet, and I don't care if these data will be imported let's say in columns A B C, then once it reaches the 65K, it will be imported to the neighbouring columns D E F, etc.?


    ...So, I hope that someone can help me out, because I really would hate to use Access.


    I'm not trying to step on toes here, but if your data already resides elsewhere, have you considered what you are going to do with it once it is in Excel?


    In other words, are all 65,000+ records necessary? If so, is it necessary to place them on the worksheet? Could they be read each time the worksheet is used?


    I'm not proposing any other solution per se, but frequently when the volume of data is this large, only a subset is being worked with. SQL may be an option that would work with your data as is and retrieve only what you really need.


    Just stirring the pot,