Consulting

Results 1 to 3 of 3

Thread: Solved: unable to get the transpose property

  1. #1
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location

    Solved: unable to get the transpose property

    Hello I have a declaration

    [vba]
    Dim MyArr As Variant
    MyArr = Application.WorksheetFunction.Transpose(ThisWorkbook.Sheets("Headers").Rang e("B14", Cells(LastRowNume, 2).Address).SpecialCells(xlCellTypeConstants))[/vba]
    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

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    [vba]
    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)
    [/vba]
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    morning, mancubus

    In the end I putted like this:


    [vba]
    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

    [/vba]
    ps. thx for your solution
    ps (the second). http://www.mrexcel.com/forum/showthr...02#post3013702 cross post

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •