Copy and paste a calculated range - transpose and paste values ignoring "0" formula results.

Hi

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.

J

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

Hi Carim,

Thank you for the prompt response and for you continued dedication to helping us on this site. Not sure what you meant with looking for a patch?

Anyway, I tried the code but got a Runtime Error - Type Mismatch on line 13..

Any clues?

Cheers

J

Hi Carim

Yes this is perfect thank you so much.

Enjoy the rest of your day.

Jan