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.
 
Back
Top