?
Solved

Copy and Paste without the empty Cells

Posted on 2012-04-04
7
Medium Priority
?
273 Views
Last Modified: 2012-04-05
I am working on a little project where I manually copy a row and paste it manually into our master calendar. I usually highlight from Column A and copy to the last column with information, which is usually Q,R or S. All in all that is just fine, I don't necessarily need to automate the entire process, but my biggest trouble is when I go to paste the data into the master calender, I get an extremely long row to cells that doesn't fit very well, It ends up with some cells with data and a lot of cells the are blank and take up a lot of space. I tried auto fit to content, but it doesn't help that much. So, I have to go in and delete each cell until I just have the cells with data in them.

Is there any way to highlight my range (the contents I need to copy), then have excel look at it and only copy the cells with data into them?

I attached a simple sheet to help show what I am trying to fix. The amount of information filled out varies from row to row depending on the Request type (Column C). I am ok with having to manually highlight the area I want to copy, but I could also work out a message box to determine which row the user wants copied.

I am trying to keep it simple and then I can add bells and whistles after it is productive.

Thanks
Brentexpert-exchange-copy-and-paste.xls
0
Comment
Question by:bvanscoy678
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Matt V
ID: 37807948
Try doing a paste special and only paste the values.
0
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 37808071
Does this get you any closer to where you want to be?

You could replace rngNoBlank.Select with rngNoBlank.Copy but be careful, if you have a non-contiguous range across multiple rows it will throw an error.  If you selection is always from a single row you will be fine.
Sub SelectNonBlanks()
Dim rngNoBlank As Range
Set rngNoBlank = Union(Selection.SpecialCells(xlCellTypeConstants), _
                        Selection.SpecialCells(xlCellTypeFormulas))
rngNoBlank.Select
End Sub

Open in new window

Kyle
0
 

Author Comment

by:bvanscoy678
ID: 37808402
Matt,

Paste Special still shows blanks.

Kyle,

I tried it both ways. I see how it highlights the cells that has values for the blanks and it copies for the copy version.

It worked better than originals, but still shows several blanks. I'll play with it a bit and check back.

Thanks.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:kgerb
ID: 37808451
Are you sure the cell it's missing is actually blank?  With the current code, the only cells that will be selected/copied are truly blank cells or cells with formulas.  Maybe the cell contains a formula that evaluates to an empty string "" so it just looks emtpy.  Just a thought.

Kyle
0
 

Author Comment

by:bvanscoy678
ID: 37808668
I will have to check on that. More than likely there is a formula in the cell.

I am going to play with this tonight a bit and see if I can also format it as part of the macro.

Thank you for the help.

Brent
0
 

Author Closing Comment

by:bvanscoy678
ID: 37808670
Thank you!
0
 

Author Comment

by:bvanscoy678
ID: 37810803
Kyle,

I did quite a bit or reading last night and to be fair about it, I am going to post an additional questions with points. It goes beyond the original question.

Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question