Solved: unable to get the transpose property

[white_flag]

Hello I have a declaration


Dim MyArr As Variant
MyArr = Application.WorksheetFunction.Transpose(ThisWorkbook.Sheets("Headers").Range("B14", Cells(LastRowNume, 2).Address).SpecialCells(xlCellTypeConstants))
but if I have a empty row between the rows then I received error 1004 ..unable to get the transpose ..etc
ex.
row1
row2
'empty row
row4
row5
row6

when I have no empty row between rows everything is ok
row1
row2
row3
row4
row5
row6

how can I tell to VBA that if it is an empty row to declare Myarr without that row

[mancubus]

Dim rng As Range, cll As Range
Dim MyArr() As Variant
Dim i As Integer

Set rng = Range("B14:B" & LastRowNume)

For Each cll In rng
If Trim(cll.Value) <> "" And Not cll.HasFormula Then
ReDim Preserve MyArr(i)
MyArr(i) = cll.Value
i = i + 1
End If
Next cll

Range("G1").Resize(UBound(MyArr) + 1, 1) = Application.Transpose(MyArr)

[white_flag]

morning, mancubus

In the end I putted like this:



Dim nameSheet As Variant, MyArr
Dim rCell As Range
For Each rCell In ThisWorkbook.Sheets("Calculation").Range("B14", Cells(LastRowNume, 2).Address).SpecialCells(xlCellTypeConstants)
MyArr = rCell.Value & "," & MyArr
Next rCell
nameSheet = Split(MyArr, ",")
For i = 0 To UBound(nameSheet) - 1
If Not Evaluate("=ISREF('" & nameSheet(i) & "'!A1)") Then
ThisWorkbook.Sheets(nameSheet(i)).Copy After:=wb.Sheets("Sheet1")
End If
Next i


ps. thx for your solution
ps (the second). http://www.mrexcel.com/forum/showthread.php?p=3013702#post3013702 cross post :)