View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips[_6_] Bob Phillips[_6_] is offline
external usenet poster
 
Posts: 11,272
Default Unable to use Range.End(xlDown) method

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)

"ved prakash" <ved wrote in message
...
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.


"Bob Phillips" wrote:

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)

"vedpatel" wrote

in
message ...

Hi,

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

Multiple Queried will be executed in generateReport(). I need to know
the last filled cell address so that I can start next value filling
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 that
xldown value is -4121 and this method will return last row cell
address. i.e. $A$65536. so if I tried to move to next row (Set
Range.Offset(2, 0)) then runtime error will be displayed. Who to
resolve this problem? Is there any other method to get last filled

cell
address?

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

Thanks
Ved


--
vedpatel


------------------------------------------------------------------------
vedpatel's Profile:

http://www.excelforum.com/member.php...o&userid=31987
View this thread:

http://www.excelforum.com/showthread...hreadid=517208