Error using transpose

[AlexanderBB]

How can I print a range variable in the immediate window? Excel VBA

I'm attempting something that should be very simple. However I've just started learning today and and can't quite understand. This is my code so far: Public Sub getCellData() Dim wb As Workboo...

stackoverflow.com

Using a suggestion above I'm trying to put the values in a range into a csv string.
But I get error 5 invalid call on the transpose line.

Function CROK(excelRow)
 Dim cc As Integer
    Dim cr As Range
    Dim strData As String
    Dim wsf As WorksheetFunction: Set wsf = Application.WorksheetFunction
    cc = 95
    With Worksheets("Sheet1")
        Do
            cc = cc - 1
        Loop Until .Cells(excelRow, cc) > " 'get first populated cell moving from column CP back
      
        Set cr = .Range("Y" & excelRow & ":" & colLetter(cc) & excelRow)
        strData = Join(wsf.Transpose(cr.Value), ",")
        Stop
    End With
End function

What am I doing wrong ?

 

[Fuji]

Join function only accepts single D array.
When the array populated from single row with multiple columns (2D array) and need to convert it to a single D array, need to transpose twice.
e.g

Function CROK(excelRow As Long) As String
    Dim C&, wf As WorksheetFunction
    Set wf = WorksheetFunction
    With Sheets("sheet1")
        C = .Evaluate("max(if(" & excelRow & ":" & excelRow & "<>"""",column(1:1)))")  '<-- find last used column index
        CROK = Join(wf.Transpose(wf.Transpose(.Range("y" & excelRow, .Cells(excelRow, C)))), ",")
        'or could use Evaluate method like
        'CROK = Join(.Evaluate(.Range("y" & excelRow, .Cells(excelRow, C)).Address & "&"""""), ",")
    End With
End Function

 

[AlexanderBB]

Great, thanks Fuji.
The C= line returns the last cell, but data may exist after col CP. I need the last used cell in Y-CP.
Maybe there still a way to use .Evaluate without my loop?
Once all working I was going to handle nothing in Y-CP.

 

[AlexanderBB]

I found you can't join if C is 25 meaning one value only in col Y.

 

[Fuji]

Do you mean like this?

C = .Evaluate("max(if(y" & excelRow & ":cp" & excelRow & "<>"""",column(y:cp)))")  '<-- find last used column index within col.Y:CP

 

[Fuji]

I found you can't join if C is 25 meaning one value only in col Y.

Then

Function CROK(excelRow As Long) As String
    Dim C&, wf As WorksheetFunction, x
    Set wf = WorksheetFunction
    With Sheets("sheet1")
        C = .Evaluate("max(if(y" & excelRow & ":cp" & excelRow & "<>"""",column(y:cp)))")  '<-- find last used column index
        If C > Columns("y").Column Then
            CROK = Join(wf.Transpose(wf.Transpose(.Range("y" & excelRow, .Cells(excelRow, C)))), ",")
            'or could use Evaluate method like
            CROK = Join(.Evaluate(.Range("y" & excelRow, .Cells(excelRow, C)).Address & "&"""""), ",")
        ElseIf C = Columns("y").Column Then
            CROK = .Range("y" & excelRow)
        End If
    End With
End Function

 

[AlexanderBB]

Yes indeed.. many thanks
I like the way you handled c in msg 5

 

[Fuji]

Glad you like it and thank for the feedback.

Yep, there's more than one way to skin a cat.