unable to get the transpose property

W

white_flag

Active Member
Joined
Mar 17, 2010
Messages
331
Hello I have a declaration

Code: 
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

crosspost :
http://www.vbaexpress.com/forum/showthread.php?t=40638
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Transpose expects a contiguous range. You could copy your selection and paste special transpose into some spare columns. Then assign that range to your variable.
 
Upvote 0
to make a contiguous range from a non-contiguous range in memory can be done? or it is stupid, what I sayed
 
Upvote 0
thank you. In this case I have to change the principle of code:
Code: 
    Dim lNum As Long
    Dim rCell As Range
    For Each rCell In ThisWorkbook.Sheets("Calculation").Range("B14", Cells(LastRowNume, 2).Address).SpecialCells(xlCellTypeConstants)
        If rCell = "" Then
        ' no idea        Else
        MyArr = rCell.Value & "§" & MyArr 'result SheetData, Sheetvalue1 etc..
        lNum = lNum + 1
        End If
    Next rCell

like that I will have the MyArr but now this MyArr need to become "feed for" another sheet:
before was:

Code: 
       For Itm = 1 To ubound(MyArr)
        If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
           ThisWorkbook.Sheets(MyArr(Itm)).Copy After:=wb.Sheets("Sheet1")
           'wb.Sheets(MyArr(Itm)).Columns.AutoFit
        Else                                                      'clear sheet if it exists
            Sheets(MyArr(Itm)).Move After:=Sheets(Sheets.Count)
            'Sheets(MyArr(Itm)).Cells.Clear
        End If
    Next Itm

but now?

how can be done create worksheets based on Array splited with character "§" any Idea?
 
Upvote 0
morning, I was trying to create some sheets from a range column in mastret workbook to a new file.

I "fixed" like this:

Code: 
    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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Error 1004 VBA Unable to get StDev property of Worksheet Class
Replies
3
Views
420
RoryA
RoryA
K
  • Solved
problem with CountIf VBA (getting 'unable to get the CountIf property' error...)
Replies
3
Views
1K
Legacy 143009
L
L
  • Question
VBA code to copy data from Excel by rows to multiple text files
Replies
1
Views
1K
Fluff
Fluff
K
  • Solved
Filter on multiple criteria values
Replies
3
Views
890
Fluff
Fluff
I
  • Solved
Unable to get the specialcells property of the range class
Replies
4
Views
2K
jasonb75
J

Forum statistics

Threads
1,215,105
Messages
6,123,118
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top