Solved

Copy and Paste without the empty Cells

Posted on 2012-04-04
7
265 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

920 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

17 Experts available now in Live!

Get 1:1 Help Now