topic Macro - find last column in Excel file in QlikView

Macro - find last column in Excel file

gerhardl — Mon, 15 Sep 2014 13:18:31 GMT

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

Re: Macro - find last column in Excel file

m_woolf — Mon, 15 Sep 2014 16:58:30 GMT

try -4159 instead of -4161 for xltoleft

Re: Re: Macro - find last column in Excel file

gerhardl — Tue, 16 Sep 2014 05:41:58 GMT

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")?

Re: Macro - find last column in Excel file

marcus_sommer — Tue, 16 Sep 2014 06:46:53 GMT

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