Application.Transpose

[AlexanderBB]

This was recommended as a way to build an array from Values in a Worksheet row, In this example Col Y to AF in row chRow

Dim Ary as variant
 Ary = Application.Transpose(Application.Transpose _
             (Worksheets(.Name).Range("Y" & chRow & ":AF" & chRow).Value))

If works great but I've struck a problem. If only Col Y contains a value Ary is a string.
My code fails as it expects an array.
What is a good way to resolve this ? I can test isArray(ary) and perhaps force the string into an array but it doesn't seem right.

 

[StephenCrump]

I don't understand the problem.

With this code, ary will necessarily be an 8-element array.

If Range("Y" and chRow) contains a text value, and Z:AF are empty, ary will still be an 8-element array, but the last seven elements will be blank.

How is your code failing? Are you perhaps using:

Ary = Application.Transpose(Application.Transpose _
             (Worksheets(.Name).Range("Y" & chRow & ":Y" & chRow).Value))

 

[AlexanderBB]

Ah yes. I used "AF" in msg 1 but the actual column varies between rows and in this case is Y.
I'm looping back through the row to get the last used column (between Y and CQ).

 

[StephenCrump]

I'm looping back through the row to get the last used column (between Y and CQ).

Hmm, that rings a bell from a couple of your posts several months ago!

The best solution probably depends on what you intend doing with this range.

But the possibilities include:

If .... 'Last used column is Y then
    'do something with Range("Y" & chRow)
Else
    ary = ...
    'do something with ary
End If

or

Ary = Application.Transpose(Application.Transpose _
        (Worksheets(.Name).Range("Y" & chRow & ":CQ" & chRow).Value))

'Loop through ary to determine last used column,
'where i can vary from 1 (if column Y) to 71 (if column CQ)
'and deal with these i elements in a loop

 

[AlexanderBB]

Thanks for the suggestions.
Did you give me this

''run the fix
' With Intersect(UsedRange, Range("Y" & erow & ":CP" & erow))
' 'xxx is something that won't be found in the range. Change if necessary!
' .Replace What:="", Replacement:="xxx", Lookat:=xlWhole, MatchCase:=False
' .Replace What:="xxx", Replacement:="", Lookat:=xlWhole, MatchCase:=False
' End With

I was going to use that again but I don't need (for this)
Set irange = Range(Range("Y" & erow), Range("CQ" & erow).End(xlToLeft))
only the last used column.
Now I'm trying

 For i = 94 To 25 Step -1
                If Trim(.Cells(chRow, i)) > "" Then Exit For
            Next
            If i = 24 Then Stop 'error handling todo

            WeekDat = Application.Transpose(Application.Transpose _
             (Worksheets(.Name).Range("Y" & chRow & ":" & colLetter(i) & chRow).Value))

          If IsArray(WeekDat) = False Then
            w1(1) = WeekDat
            WeekDat = w1
          End If

This gives an Array to use unless it's empty which is a todo
I think there's another way to directly fill an array from a row without Application Transpose
but I can't find it.

 

[StephenCrump]

I think there's another way to directly fill an array from a row without Application Transpose

This will create a 1 x 71 array, so you'll need to refer to element (1, i). The TRANSPOSE(TRANSPOSE) construction just converts this to a one dimensional 71-element array, so that you can refer to element (i).

With ws
    ary = .Range("Y" & chRow & ":CQ" & chRow).Value
End With

Did you give me this ...

Yes, in this thread, when you were having trouble with rogue blank characters: Last Col in Range

Is this working OK now?

Set irange = Range(Range("Y" & erow), Range("CQ" & erow).End(xlToLeft))

If so, you could do something like this. (Or alternatively, if the .End(xlToLeft) is still causing problems, loop backwards through ary to determine N).

With ws
    ary = .Range("Y" & chRow & ":CQ" & chRow).Value
    N = .Range(.Range("Y" & erow), .Range("CQ" & erow).End(xlToLeft)).Columns.Count
    For i = 1 To N  'where N could be 1, if Y is the last populated column
        'do something with ary(1,i)
    Next i
End With

'or

With ws
    ary = .Range("Y" & chRow & ":CQ" & chRow).Value
    N = .Range(.Range("Y" & erow), .Range("CQ" & erow).End(xlToLeft)).Columns.Count
    ReDim Preserve ary(1 To 1, 1 To N)
    For i = 1 To UBound(ary, 2) 'again, this could be 1
        'do something with ary(1,i)
    Next i

End With

 

[AlexanderBB]

I did get the routine for LastCol working ok, thanks.
I've been doing much redesigning over the last 24 hours and don't need Range Y-CQ for this procedure.
Now I'm setting a range for just the cols needed and using .count to read the values
But I do have a question sbout speed,
Col V holds a start date and Col X and end date
if wanted date (dd) is between start and end that row number and Col "C" does into adictionary.
The way the data is spread I have to do this over 2 worksheets
This is what I've come up with but is slow.

 If cSheet = "Sheet1" Or cSheet = "Sheet2" Then
                With Worksheets("Sheet1")
                For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
                    If CLng(CDate(.Cells(i, "V"))) <= dd Then
                        If CLng(CDate(.Cells(i, "X"))) >= dd Then
                            CH.Add i & " " & .Cells(i, "C")
                        End If
                    End If
                Next
            End With
            With Worksheets("Sheet2")
                For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
                    If CLng(CDate(.Cells(i, "V"))) <= dd Then
                        If CLng(CDate(.Cells(i, "X"))) >= dd Then
                            CH.Add i & " " & .Cells(i, "C")
                        End If
                    End If
                Next
            End With
end If

Is there any way to gte that to run faster?
Sheet1 has 18000 rows and sheet2 16000.
I tried a Range but (unless I did it wrong) that took much longer.

 

[StephenCrump]

So this is a totally different question? If so, you'd have been better off posting a new thread, as you're more likely to get help on an unanswered thread.

Your code construction implies that you're looping through the worksheets? If so, this code will run twice on Sheet1 and Sheet2, once when cSheet is Sheet1, and the second (unnecessary) time when cSheet is Sheet2.

If cSheet = "Sheet1" Or cSheet = "Sheet2" Then
    With Worksheets("Sheet1")
        '....
    End With
    With Worksheets("Sheet2")
        '...
    End With
End If

The slow part of this code will be reading potentially up to around 100,000 individual cells one by one. I'd use VBA to filter the two sheets on columns V and X, take just the filtered column C into VBA as a single array, and use this to populate your dictionary.

Others might prefer taking the entire range C:X into VBA as an array, and doing the date checks in VBA.

The code execution speed may depend on other factors - size of workbook, calculations being done, the code you haven't shown, including any other code that may be being triggered, whether you have set .Calculation to xlCalculationManual, turned off .ScreenUpdating etc.

PS - not quite sure why you're using CLng(CDate(..)), or why you're concatenating i & " " to the dictionary value?

 

[AlexanderBB]

That was interesting, never realised it'd run twice.
Although cSheet will only be one thing each time. My logic was to do both if it was either sheet1 or Sheet2.
I'll make some changes and compare timings. Still thinking about filtered C or range C:X. Will try. Must be worthwile to reduce 100,000 individual cells.
Probably wasn't obvious but dd is a long, hence the CLng(CDate(..)), compare. And I need two bits of data in each dictionary
entry. Might not have needed a dictionary (collection?) but It was easy to use.
Great to discuss like this and get new ideas.

 

[MARK858]

Probably wasn't obvious but dd is a long, hence the CLng(CDate(..)), compare. And I need two bits of data in each dictionary

I think @StephenCrump is puzzling why you need the CDate, if it is a real date can't you just do

CLng(.Cells(i, "V"))