+ Reply to Thread
Results 1 to 5 of 5

VBA selection choosing thousands of extra cells

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    528

    VBA selection choosing thousands of extra cells

    Hello,

    I have a spreadsheet with a userform to manipulate data.

    The userform copies in data from Sheet2 into Column J of Sheet3. In Column K I have =J2(Right,10) formula copied down to around row 200,000 - as there are occasions where I may be working with this much data.


    A macro copies the data from Column K into Column L, where it is pasted as Values. This is then copied into Column A of Sheet1, where a Macro creates a PivotTable from the data.

    However, the main issue I have is that the pivot created looks at a number of blanks, as it will create data from Column A in Sheet1 from A1 down to A200000.


    I have tried running code to delete blanks from COlumn A before creating the PivotTable, but it still goes up to A200000.


    Any ideas how to get around this?

    Kind Regards,

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,940

    Re: VBA selection choosing thousands of extra cells

    If you save the file, inside your code, after deleting the not needed rows, does that help?

  3. #3
    Registered User
    Join Date
    05-08-2019
    Location
    Detroit
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: VBA selection choosing thousands of extra cells

    I had a similar issue with the used area and getting a formula pasted to the used column next to it...

    The following goes to the top cell below my title in row O and writes the formula (in your case =J2(Right,10)), it then copies it based on data in column N, pasting this formula until the end (plus one row) then I have it delete the last item in the list that was extra.
    Please Login or Register  to view this content.
    ct
    Selection.Delete

    Not sure this fixes your full issue, but it will fix the need for including 200k line items.
    Last edited by alansidman; 05-10-2019 at 04:59 AM.

  4. #4
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    528

    Re: VBA selection choosing thousands of extra cells

    What would I put for my formula on the 3rd line? Not sure how it would go in the code.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    20,186

    Re: VBA selection choosing thousands of extra cells

    @BILLONEILL40
    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Concatenate thousands of cells with a comma between each one?
    By MetroBOS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2016, 08:27 AM
  2. [SOLVED] Delete Thousands of Extra Columns
    By gobeavers in forum Excel General
    Replies: 15
    Last Post: 12-05-2012, 02:36 PM
  3. Replies: 3
    Last Post: 09-20-2012, 03:11 PM
  4. Replies: 8
    Last Post: 08-27-2012, 04:37 PM
  5. copy and pasting thousands of cells.
    By Merickson04 in forum Excel General
    Replies: 3
    Last Post: 06-19-2008, 09:14 AM
  6. Adding together thousands of sets without thousands of formulas!
    By Ratiocination in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2008, 10:29 AM
  7. [SOLVED] need to add/remove characters over thousands of cells
    By Nadia in forum Excel General
    Replies: 3
    Last Post: 06-19-2005, 07:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1