Solved

Copy and Paste without the empty Cells

Posted on 2012-04-04
7
264 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
  • 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now