+ Reply to Thread
Results 1 to 2 of 2

Copy and paste a dynamic range and remove blanks

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

    Copy and paste a dynamic range and remove blanks

    Hi all,

    I currently have a macro that copies the whole of Column K, which is dynamic and could be from K2:K10 or K2:K100000.

    This data is copied and pasted into Column L and it is sorted and the blank cells removed. This data is then copied to A2 of Sheet1, where a Pivot is then created from this data.

    However, say for example the data is K2:K4242 with a number of these being blank. When this is copied from Column L into Sheet 1 the Pivot still selects K2:K4242 despite the last 2000 cells being blank.

    THis then causes problems for the pivot, as it counts the blanks etc.

    Any ideas how to get around this?

    Kind Regards,

    Chris

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Copy and paste a dynamic range and remove blanks

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ 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. vba to copy and paste range without blanks
    By Throughstream in forum Excel General
    Replies: 1
    Last Post: 09-12-2018, 10:43 AM
  2. [SOLVED] Formula to count blanks and non-blanks with a dynamic range
    By brittdyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2018, 07:45 AM
  3. [SOLVED] Copy & Paste Columns to Specific Range while Removing Blanks & Duplicates
    By dondada82 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-15-2017, 11:31 AM
  4. [SOLVED] Dynamic Array Validation to remove blanks
    By welshben in forum Excel General
    Replies: 0
    Last Post: 02-17-2016, 07:12 AM
  5. [SOLVED] Copy and paste random cells values from a range to another sheet column with no blanks
    By macquhele in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2015, 06:05 PM
  6. Remove Blanks With Dynamic Named Range - Error with the formula
    By Tepsjen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-23-2014, 05:34 PM
  7. Copy Rows - Paste and remove blanks
    By tigerdel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2012, 01:28 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