Solved

Copy and Paste without the empty Cells

Posted on 2012-04-04
7
269 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 500 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
Industry Leaders: 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

Technology Partners: 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!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

732 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