Copying non-blank cells in a range - XL97

S

Steve Jones

I have a range say A1:M50. I would like to select a row say A1:M1 and copy
the non-blank cells and transpose the values to another sheet. The blank
cells would vary from row to row.

Thanks as always,
Steve
 
T

TroyW

Steve,

Below is some basic VBA code to accomplish what you want. You can also
accomplish by selecting the entire range of cells and then from the menubar:
Edit | Goto , click the Special button, click the Contants radio button,
click OK That will select the non-blank cells. Then use Copy, and Paste
Special with "Skip Blanks" and "Transpose" checked.

Troy


Range("A1:M1").SpecialCells(xlCellTypeConstants, 23).Copy
Range("A11").PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=True, _
Transpose:=True

Application.CutCopyMode = False 'Cancel marquee box.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy addition formula of non-consecutive horizontal cells 2
Eliminate blanks while copy row and transpose 11
Copy .Areas.Item(1,2,3,4,5, etc.) to a column or a row 5
Need Help with a VBA subroutine 0
Copy columns and paste with blank column between 5
Skip copying empty column where remaining rows are filled 2
Concatenate a row that is selected and include blank cells 11
Dealing with a Pivot Table (when I don't want to) 0

Top