+ Reply to Thread
Results 1 to 3 of 3

Copy non-blank cells in a range and paste all into a single column

  1. #1
    Registered User
    Join Date
    06-27-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    13

    Question Copy non-blank cells in a range and paste all into a single column

    Hi, all! I did some searching on the Forum and wasn't able to find an answer that fits this particular situation. I have a large data set of disease types spread across many different columns. I'm trying to take the entire range, copy the non-blank ones (i.e. the cells that contain a disease type), and paste this entire range into a single column. I can do everything except for the pasting into a single column. Kutools lets me copy all the non-blank cells, but then it pastes them into the same configuration as they were originally written (i.e. in a bunch of different columns), when what I need for further analysis is to have all the data in one column. Any ideas? Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,649

    Re: Copy non-blank cells in a range and paste all into a single column

    AR2=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(INDEX(($A$2:$AP$23="")*10^15+(COLUMN($A$2:$AP$2)&TEXT(ROW($A$2:$A$23),"0000")&(TEXT(COLUMN($A$2:$AP$2),"0000"))),0),ROWS(AR$2:AR2)),8),"\R0000\C0000"),0),"")
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    06-27-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    13

    Re: Copy non-blank cells in a range and paste all into a single column

    That worked like a charm. Thanks very much!

+ 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 copy range of cells, paste on next blank cell down
    By ahalliwell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-22-2018, 10:46 AM
  2. Replies: 12
    Last Post: 03-05-2016, 03:03 PM
  3. Copy Range and Paste only Values (ignore blank cells)
    By ksayet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2015, 04:37 PM
  4. [SOLVED] copy only non-blank cells in range and paste to new range.
    By scrabtree23 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2015, 08:10 PM
  5. [SOLVED] Copy / Paste range from one sheet to another, if cell in column A is not blank
    By Tapyr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2014, 09:18 AM
  6. Copy range and paste as value and clearcontants of blank cells and number
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2014, 06:59 AM
  7. Macro. Copy range of cells (e,g, =A1:B9) and paste it into single cell.
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2011, 09:28 AM

Tags for this Thread

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