Solved Access VBA To Concatenate Excel Cells (1 Viewer)

[jo15765]

Change

lColumn = ws.range("A" & ws.columns.count).end(-4159).column

to

lColumn = ws.range("XFD1").end(-4159).column

That seems to be giving valid values now...
last row = 1728
lColumn = 7

 

[Isaac]

Yay! Are there any errors?

 

[jo15765]

ok thanks, see my previous post about the change

YES! That is it! Thank you for your patience and support, it is much appreciated!

 

[Isaac]

Glad to help and best of luck w/your project.

 

[Isaac]

Today in my own work I was reminded of a particular thing that can happen when using .End to determine that "last" row, or column, of data. There are scenarios whereby that method will take you to the edge of a previously used range which no longer has data (such as a Table that extends downward, empty, past the real filled rows of data). In that case the .End will take you to an empty cell actually, and ANOTHER .End is required to get all the way "up" (or "left"). Due to this, it's probably safer to use a method like this:

lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
If Len(Cells(lastrow, 1).Value) = 0 Then
    'do it again, this time start from lastrow
    lastrow = ws.Range("A" & lastrow).End(xlUp).Row
End If

... Just thought I ought to come back and make the adjustment to my recommendation.