Unable to use Range.End(xlDown) method

[vedpatel]

Hi,

The scenario is like this. I have one .Xla file which has a functio
called generateReport(). This function will retrieve the data fro
Database and display in excel sheet.

Multiple Queried will be executed in generateReport(). I need to kno
the last filled cell address so that I can start next value fillin
form the next row. For this purpose I have used Range.End(xlDown
method to know the last filled cell. But while debugging I found tha
xldown value is -4121 and this method will return last row cel
address. i.e. $A$65536. so if I tried to move to next row (Se
Range.Offset(2, 0)) then runtime error will be displayed. Who t
resolve this problem? Is there any other method to get last filled cel
address?

This .Xla file function will be called from VB application.

Thanks
Ve

 

[Bob Phillips]

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Yes i have tested the below code but still it was going to select the last
row cell i.e. A65536. I have created a range type variable and set the range
of the worksheet like this "Set lv_aRange = p_infoSheet.Range("A1")". Now i
have filled some row values (may be 4 or 5 rows values, it can be some thing
else). now if i execute the below statement "Set lv_aRange =
lv_aRange.End(xlDown).Select" then it was throwing an rum time error. and the
selected cell address is A65536.

This can happen if the column is empty. You can test for it

If Application.CountA(Columns("A:A")) = 0 Then
Range("A1").Select
Else
Range("A1").End(xlDown).Select
End If


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

You cannot use Select within a Set statement. Set creates an object, Select
is going to a particular cell, they have no correlation.

You need

Set lv_aRange = lv_aRange.End(xlDown)

but this only creates a range to the cell at the end, not all cells from
start to end, for that you need

Set lv_aRange = Range(lv_aRange, lv_aRange.End(xlDown))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Yes i am getting the Range from start to end, but my requirement is to get
the address of last filled cell address and start filling the next values
from there onwards.
i am using the below method to fill next values once i found the last filled
cell address.
Set lv_aRange = lv_aRange.Offset(2, 0)
And start filling the values to worksheet. while executing the above
statement it throws run time exception " err No :1004, Error Description :
Application-defined or object-defined error."
Please help to resolve this issue.

Thanks,
Ved

 

[Bob Phillips]

where does lv_aRange point to, and what is the data in there?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

In One common template .Xla file i am opening one more excel workbook and
pointing the range to that workbook. The data will be retrieved from
database. while debugging i can see the data were filled in 2-3 cells and
when i am trying to ge the last filled cell address then it gives me last
cell address of the worksheet.