I have data that sums across columns based on column header. I need to take these totals and transpose to a single column without the 0 results.
Currently the code (thanks to another user) results in a blank cell where the formula result is zero.
How can I transpose the data so that there are no blank (zero) cells in the resulting list? Pls refer screenshot for desired result
I thought an If > 0 statement might work but I got the same result. Skip blanks is also not applicable as there are results for the formula.
Many thanks for your help once again.
Sub transposeColumns() Dim R1 As Range Dim R2 As Range Dim R3 As Range Dim RowN As Integer wTitle = "Transpose multiple rows/columns" Set R1 = Application.Selection Set R1 = Application.InputBox("Select the Source data of Ranges to be copied:", wTitle, R1.Address, Type:=8) Set R2 = Application.InputBox("Select one destination Cell or column:", wTitle, Type:=8) RowN = 0 Application.ScreenUpdating = False For Each R3 In R1.Rows R3.copy R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True RowN = RowN + R3.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub