Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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         

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Contributor III
Contributor III

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.

r_vdbraak_0-1603274418231.png

Thanks in advance.

Richard

 

r_vdbraak
Contributor III
Contributor III

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"