Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove duplicates except blanks | Excel Worksheet Functions | |||
remove blanks using formula | Excel Worksheet Functions | |||
transpose without 0's and blanks | Excel Discussion (Misc queries) | |||
Remove blanks | Excel Programming | |||
Remove blanks from list via VBA | Excel Programming |