I have this sample table.
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.