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
- R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
- RowN = RowN + R3.Columns.Count
- Application.CutCopyMode = False
- Application.ScreenUpdating = True
- End Sub