How to find Last row in a given column of excel sheet using Qlikview macro

[Anonymous]

Hi All

I am very new to Qlikview 

Is there any way to find the last used row in a given column of excel sheet by using the Qlikview macro I used several VBA code as well as the vbscript codes

some of them are listed below but none of them worked

1. ExcelWB.Activesheet.UsedRange.Cells.Find("*", [A1],,,xlByRows,xlPrevious,,,).row
2. ExcelWB.Activesheet.Cells(.Rows.Count, FirstCell).End(xlUp).Row
3. ActiveSheet.Range("B:B").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Please help me

Regards

Harsha         

[Gysbert_Wassenaar · Accepted solution]

In vbscript you don't have direct access to the excel enumerations, so xlUp can't be used. Try its value -4162 instead:

ExcelWB.Activesheet.Range("A65536").End(-4162).Row to find the last row in column A.

Edit: There's a list of enumerations on msdn. Or you can look them up in the object browser of the vba ide of excel.

[Anonymous]

Hi Gysbert

Thankyou it's working fine

can you please let me know how i could get to know all such enumerations for Qlikview Macro's please ....

Regards

Harsha

[Anonymous]

Hi Gysbert

Can you help me in finding last column in a given row

below code is throwing some error

myCol = Obj_XLTemplate.worksheet("sheet1").Range("A1").End(-4161).Column

Regards

Harsha

[r_vdbraak]

Gysbert,

Can you tel me how I can put my data in the first empty column.

When I replace intExcelLastColumn by a number the code works fine.

Thanks in advance.

Richard

[r_vdbraak]

Gysbert,

Is there also a way to look for the last column in row 2.
I have an Excel file with the headers already in row 1.

I tried with "A2" but with the same result as "A1"