Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy and Paste without the empty Cells

Posted on 2012-04-04
7
Medium Priority
?
275 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

618 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