Copying non-blank cells in a range - XL97

  • Thread starter Thread starter Steve Jones
  • Start date Start date
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
 
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

Eliminate blanks while copy row and transpose 11
Copy .Areas.Item(1,2,3,4,5, etc.) to a column or a row 5
VBA set thick borders around non empty cells in range 0
Copy columns and paste with blank column between 5
Concatenate a row that is selected and include blank cells 11
UnSelect the copied to range after copy... 4
Array with many cells from a row but not in order or together 11
Not seeing cells as blank 2

Back
Top