Avatar of prodempsey
prodempseyFlag for United States of America asked on

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

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
Microsoft ExcelVBA

Avatar of undefined
Last Comment
prodempsey

8/22/2022 - Mon
Martin Liss

Put a breakpoint on this line.
arr = WS.Cells(1, LastRow)

What is the value of LastRow? And if you need more help debugging then please see this article of mine.
Martin Liss

If your way of calculating the LastRow isn't working then try this

LastRow = Range("A1048576").End(xlUp).Row

BTW you have the Dim for LastRow commented out. Why is that?
ASKER
prodempsey

The value of LastRow is 3.  I attached a screenshot.
Screenshot_1.png
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
prodempsey

I just commented out the Dim for LastRow because I was testing another way to make it work.  I forgot to uncomment it before pasting it into this thread.
ASKER
prodempsey

It's storing the correct value for LastRow, I think it's something going on with the arr variable
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
prodempsey

Yup! That was it.  Your the man Sktneer.  Thanks for your help again!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Subodh Tiwari (Neeraj)

You're welcome. Glad I could help. :)
ASKER
prodempsey

In case anyone is interested, I used this code to transpose a row of phone numbers entered into column A, and output to notepad with commas between each phone number.  Here is the code with the accepted solution, and I attached the final spreadsheet.
Sub PhoneNumbersWithCommas()
Dim FF
Dim arr
Dim sFileName As String
Dim sDefaultPath As String
Dim LastRow As Long
Dim WS As Worksheet
Dim rng As Range


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



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

On Error Resume Next
Set rng = WS.Range(WS.Cells(1, 1), WS.Cells(LastRow, 1)).SpecialCells(xlCellTypeBlanks)

rng.Rows.Delete Shift:=xlShiftUp

    arr = WS.Range(WS.Cells(1, 1), WS.Cells(LastRow, 1))
    
    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

Open in new window

Phone-Num-to-Commas-Script.xlsm