Transpose and remove blanks
 

I have a survey results workbook. Each time we receive a response, the
values from the response are pasted into a new column on the "Results"
worksheet.

I need to transpose one row of values on a "Results" sheet into a column of
entries on a "Summary" sheet. The range of values in the source row will
change regularly but when another value is entered, I want a new Row the
"Summary" sheet.

The challenge is that there are blank cells in the "Results" sheet source
row, which I do NOT want in the Summary sheet.

Example: On the Results sheet, I have 40 columns, 32 of which have values
in them. I have a "Summary" sheet, where I need each of the 32 values,
listed in a column.

Any help would be appreciated.

Transpose and remove blanks
 

Sub copyLastRow()
Dim rng as Range, rng1 as Range
With worksheets("Results")
set rng = .cells(rows.count,1).End(xlup)
set rng1 = .cells(rng.row,"IV").end(xltoLeft
set rng = Range(rng,rng1)
End with
with worksheets("Summary")
set rng1 = .Cells(1,"IV").End(xltoLeft).offset(0,1)
End with
rng.copy
rng1.pastespecial Paste:=xlPasteAll, Transpose:=True
end sub

--
Regards,
Tom Ogilvy


"KarenB" wrote:

I have a survey results workbook. Each time we receive a response, the
values from the response are pasted into a new column on the "Results"
worksheet.

I need to transpose one row of values on a "Results" sheet into a column of
entries on a "Summary" sheet. The range of values in the source row will
change regularly but when another value is entered, I want a new Row the
"Summary" sheet.

The challenge is that there are blank cells in the "Results" sheet source
row, which I do NOT want in the Summary sheet.

Example: On the Results sheet, I have 40 columns, 32 of which have values
in them. I have a "Summary" sheet, where I need each of the 32 values,
listed in a column.

Any help would be appreciated.