troubleshooting Question

Excel VBA - Type Mismatch - Variable - Trying to Get Last Row

Avatar of prodempsey
prodempseyFlag for United States of America asked on
Microsoft ExcelVBA
9 Comments1 Solution345 ViewsLast Modified:
I'm trying to get the last row in a single column, but I'm getting a Type Mismatch error at the line of code that say's    arr = Join(arr, ",")
What am I doing wrong?  For some reason this works when I specifically set the arr variable to Range("A1:A50"), but not when I try to dynamically find the last row.

Sub PhoneNumbersWithCommas()
Dim FF
Dim arr
Dim sFileName As String
Dim sDefaultPath As String
'Dim LastRow As Long
'Dim WS As Worksheet

sDefaultPath = Environ$("UserProfile") & "\My Documents\"
sFileName = "PhoneNumbers.txt"
Set WS = Worksheets("Sheet1")

LastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row

    arr = WS.Cells(1, LastRow)
    arr = Application.WorksheetFunction.Transpose(arr)
    arr = Join(arr, ",")
    FF = FreeFile()
    Open sDefaultPath & sFileName For Output As #FF
        Print #FF, arr
    Close #FF
    RetVal = Shell("Notepad.exe " & sDefaultPath & sFileName, 1)
End Sub
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros