(Solved) – Adding and Setting Ranges in Excel VBA

  • by

I have this sample table.

enter image description here

What I am trying to do is to get all the cell values in all colored cells and transpose them to another worksheet.
I have trouble with the code below to add and set those ranges together so that I can transpose all of them in one column in the other worksheet. I have started with the code below

Sub AddRanges()

Dim inRange As Range, inRangeValues() As Variant, outRangeValues() As Variant
Dim finalRow As Long
Dim inRange As Range

Set inRange = Sheet1.Range("A1:A6", "C1:C6", C10:C14) 'I think i got this wrong; Error Type Mismatch

inRangeValues() = inRange.Value 'generate 2d array
outRangeValues = Application.Transpose(inRangeValues)

With Sheet2 
    finalRow = .Cells(Rows.Count, 1).End(xlUp).Row 'find last row

    If inRange.Columns.Count > 1 Then        '2d array for output
        .Cells(finalRow   1, 1).Resize(UBound(outRangeValues, 1), UBound(outRangeValues, 2)) = outRangeValues 'Resize according to output array dimensions
    Else                                     '1D array for output
        .Cells(finalRow   1, 1).Resize(1, UBound(outRangeValues, 1)) = outRangeValues
    End If
End With
End sub

In this example, what is the best approach to combine these ranges so I can transpose them as row? Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *