Link to home
Start Free TrialLog in
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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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.
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?
Avatar of prodempsey

ASKER

The value of LastRow is 3.  I attached a screenshot.
Screenshot_1.png
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.
It's storing the correct value for LastRow, I think it's something going on with the arr variable
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yup! That was it.  Your the man Sktneer.  Thanks for your help again!
You're welcome. Glad I could help. :)
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