Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sriharsha96
Contributor II

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
MVP & Luminary
MVP & Luminary

Re: Qlikview macro to find Last row in a given column of excel sheet

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
3 Replies
MVP & Luminary
MVP & Luminary

Re: Qlikview macro to find Last row in a given column of excel sheet

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
sriharsha96
Contributor II

Re: Qlikview macro to find Last row in a given column of excel sheet

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

sriharsha96
Contributor II

Re: Qlikview macro to find Last row in a given column of excel sheet

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

Community Browser