copy cells but miss blanks

**wonderdunder** · 10-25-2010, 03:38 PM

I need a VBA to solve the following.

Spreadsheet "customers" has seven columns as follows (from A to G):

(A) Customer Name, (B)CustID C(Address1) D(Address2) E(Address3) F(Postcode) G(Contact Name). The rows are for each customer. Not all of the cells are populated (usually address 1 or 2).

Sheet "Data Entry" uses a dropdown list at B2 that allows selection of customer name. B3 to B8 uses Vlookups to display the remaining details. Where there are blank cells on the "customers" sheet, these show as 0 on the Vlookup data.

What I'm trying to do is to copy the customer data from the Data Entry sheet (B2 to B8) but to miss out any of the blanks (or 0s). This will then be pasted onto another sheet (Analysis) From A1 to A whatever

So for example, if I have column B on Data Entry Sheet:

B2 - Acme Co (from a dropdown)
B3 - AcmeID123
B4 - Acme House
B5 - Acme Street
B5 - 0
B6 - AC11ME
B7 - Mr Acme

I would want this duplicated onto another sheet (Analysis) as follows:

A1 Acme Co
A2 AcmeID123
A3 Acme House
A4 Acme Street
A5 AC11ME
A6 Mr Acme

I know that's probably a really long winded way of explaining a simple problem, so apologies - simple describes me!

**JBeaucaire** · 10-25-2010, 04:10 PM

1) Highlight the column
2) Press F5 and click on Special
3) Select Constant > Text (uncheck the others)
4) Copy the selected cells and paste in your target location.

You can record that into a quick macro pretty easily.

**wonderdunder** · 10-26-2010, 04:47 AM

Doesn't seem to work.

When I try it, all I get is the customer name and the rest as #NA.

I've treid paste special and selecting values, but that does nto skip blanks. Even when I tick the "skip blanks" option it still pastes the zero.

Any ideas?

**JBeaucaire** · 10-26-2010, 08:07 AM

Using your list from post #1 it worked for me. Odd that it doesn't work for you.

Care to post a sample workbook showing BEFORE/AFTER so I can see what you see? Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**wonderdunder** · 10-28-2010, 08:57 AM

Hello,

Attached a sheet (with some alterations and deletions but the effects are the same - apologies for the other code left on).

You will see on the first sheet (initial) that there is the list to select a customer name at C5, and the cells below (C6 - C10) display the address details using a vlookup.

If you select customer 1, you will see that one of the lines is blank in the address. What I'd like to do is to be able to copy all of the selected customer details onto another sheet but to miss out the blanks (which appear as 0) using VBA and to paste it on anoher sheet so that the 0s are missed out.

Andy help appreciated (and apologies for the delay).

**JBeaucaire** · 10-28-2010, 09:34 AM

The technique still works, you just didn't mention the range included both formulas and constants. Like so:

Sub Copy_address3()
Dim RNG As Range

Set RNG = Union(Range("B5:E10").SpecialCells(xlCellTypeConstants, 2), _
                 Range("B5:E10").SpecialCells(xlCellTypeFormulas, 2))
RNG.Copy
Sheets("CERTIFICATE").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

Notice I've not done any "selecting" like your other macro show. It's not necessary to bring sheets up onscreen or selecting cells before issuing action commands to them. Just properly address those ranges with complete references and the selecting goes away.

**wonderdunder** · 10-28-2010, 10:03 AM

Oh thank you so much - it's been giving me a headache for yonks.

I take on board what you said about selecting - it's just that the only things I know about VBA are what I pick up from here and jiggling about with the code until I get what's needed (or at least get rid of the errors!).

Thanks again

**JBeaucaire** · 10-28-2010, 10:23 AM

That's how we all learn. Using the macro recorder is something I still do every day, the code it offers is horrible, but informative. I always tweak it way down from what it offers.

**wonderdunder** · 10-29-2010, 07:38 AM

OK - I've just tried running this at work and I'm getting an error 1004 - no cells were found.

The only change I've made is adding the following which solved the problem in locating bookmarks:

 Const wdGoToBookmark = -1

Any ideas on what's causing this? I'm getting the error on the "set rng = " part.

**JBeaucaire** · 10-29-2010, 09:50 AM

Your latest example demonstrated that the range to copy cells from had both constants and formulas in the range. So the Set RNG command is structured to copy both constants and formulas in that range. If there are NO formula cells, that would cause an error. If there were NO constant cells, that would cause an error.