Macro - find last column in Excel file

[gerhardl]

Hi,

I have a macro in which I find the last Row with data in an excel file like this:

LR = XLSheet1.Range("A65535").End(-4162).Row

I then later reference it successfully like this:

XLSheet1.Range("G" & LR).Font.Size = 16

I am now trying to replace the Column (G) with similiar logic, to use the last column with data in, but the below is not working ("Unknown Runtime Error")"

LR= XLSheet1.Range("A65535").End(-4162).Row    'Find the last Row

LC= XLSheet1.Range("A1").End(-4161).Column    'Find the last Column

XLSheet1.Range(LC & LR).Font.Size = 16

Please help with the correct way of doing this?

Gerhard

[m_woolf]

try -4159 instead of -4161 for xltoleft

[gerhardl]

Hi Michael,

That doesn't work either - please see image attached. The issue seems to be at the point where I try and reference the cell.

Is it not that the ".End(-4159).Column" returns a column NUMBER, and I am trying to use it as a column NAME (e.g. "G")?

[marcus_sommer]

You could use some kind of mapping directly in qv or in vbs or you used a function which converted a number to a string. I use since many years these:

Function Spalte1(Optional SpaltenBuchstabe As Variant) As Variant

   Dim varArr As Variant

   If IsMissing(SpaltenBuchstabe) Then _

      SpaltenBuchstabe = Range(Application.Caller.Address(0, 0)).Column

   If SpaltenBuchstabe < 1 Or SpaltenBuchstabe > 256 Then

      Spalte1 = CVErr(xlErrValue)

      Exit Function

   End If

   varArr = Split(Columns(SpaltenBuchstabe).Address(0, 0), ":") ' liefert für Spalte 1 ="A:A"

   Spalte1 = varArr(0)

End Function

But you will find many more examples here: Google

- Marcus