Unable to use Range.End(xlDown) method

**vedpatel** · 02-28-2006, 04:16 AM

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

Bob Phillips · 02-28-2006, 05:15 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

ved prakash · 02-28-2006, 06:10 AM

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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

Bob Phillips · 02-28-2006, 06:15 AM

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 [email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
in
> > message news:[email protected]...
> > >
> > > 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
> > >
> >
> >
> >

ved prakash · 02-28-2006, 08:15 AM

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" wrote:

> 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 [email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote
> in
> > > message news:[email protected]...
> > > >
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

Bob Phillips · 02-28-2006, 09:00 AM

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ved prakash" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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 [email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]>
wrote
> > in
> > > > message
news:[email protected]...
> > > > >
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

ved prakash · 03-01-2006, 12:20 AM

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.

"Bob Phillips" wrote:

> where does lv_aRange point to, and what is the data in there?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "ved prakash" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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 [email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]>
> wrote
> > > in
> > > > > message
> news:[email protected]...
> > > > > >
> > > > > > 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
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>