Unable to mark the range in VB and get application error- WHY
 

DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004- 'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

Cells start at 1,1 so stating the start of the range as 0, 0 will throw up
issues.

I've not tried your code, but that's what I get from just looking at it.

Hope that helped somewhat.

"CAPTGNVR" wrote:

DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004- 'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

Hi,

XlDown won't necessarily get you the last populated cell so I've use Xlup
instead

Public Sub LASTTROW()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A5:A" & lastrow).Resize(, 6)
myrange.Select
End Sub

Mike

"CAPTGNVR" wrote:

DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004- 'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

There is no such cell as Cell(0,0), try using Cell(1,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

Consider this....

If I fully understand your request, give this snippet a go...


Mark



LastRow = Range("A5").End(xlDown).ROW

Range("A5:E" & LastRow).Select









"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

Mr. NateBuckley, thanks for the tip. I was assuming that 0,0 meaning the
existing cell. Anyhow ur tip worked and once again thnks.

"NateBuckley" wrote:

Cells start at 1,1 so stating the start of the range as 0, 0 will throw up
issues.

I've not tried your code, but that's what I get from just looking at it.

Hope that helped somewhat.

"CAPTGNVR" wrote:

DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004- 'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

D/Mike
I tried your code and did not meet my needs as I mentioned before, I do have
in col-A, serial number 1 to 50 and again from a70 to a150.

By using xlup it takes the last row as 150 but I want it to take the row 50
in this case. For this the sugestion xldown helps.

Thank you for the sugestion of "Set myrange = Range("A5:A" &
lastrow).Resize(, 6) " which was good to note.

brgds/captgnvr

"Mike H" wrote:

Hi,

XlDown won't necessarily get you the last populated cell so I've use Xlup
instead

Public Sub LASTTROW()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A5:A" & lastrow).Resize(, 6)
myrange.Select
End Sub

Mike

"CAPTGNVR" wrote:

DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004- 'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

D/MARK

IT WORKED JUST FINE. ANOTHER COOL ONE TO USE IN THE RANGE OPTION.

THNKS AND WITH THIS I SHOULD CONSIDER THE THREAD CLOSED WITH A NICE FEELING
OF MAKING PROGRESS AND THANKING MR. NAT, MIKE AND SANDY MAN FOR THE GUIDANCE.

BRGDS/CAPTGNVR

"Mark Ivey" wrote:

Consider this....

If I fully understand your request, give this snippet a go...


Mark



LastRow = Range("A5").End(xlDown).ROW

Range("A5:E" & LastRow).Select









"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

There is no such cell as Cell(0,0), try using Cell(1,1)

Sure there is. Try

Dim Cell As Range
Set Cell = Range("C3")
Debug.Print Cell(0, 0).Address


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Sandy Mann" wrote in message
...
There is no such cell as Cell(0,0), try using Cell(1,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

While I recognize the OP has finished with this thread, I thought I would
add my comment for the archives at least. Considering the OP said he had A5
set as the selected cell; assuming selecting a starting cell before running
the macro is how the OP always proceeds, then your Range-approach solution
can be shortened down even further to this one-liner...

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Rick


"Mark Ivey" wrote in message
...
Consider this....

If I fully understand your request, give this snippet a go...


Mark



LastRow = Range("A5").End(xlDown).ROW

Range("A5:E" & LastRow).Select









"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

I stand corrected. There is no such cell as Cells(0,0)

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Chip Pearson" wrote in message
...

There is no such cell as Cell(0,0), try using Cell(1,1)

Sure there is. Try

Dim Cell As Range
Set Cell = Range("C3")
Debug.Print Cell(0, 0).Address


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Sandy Mann" wrote in message
...
There is no such cell as Cell(0,0), try using Cell(1,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

Rick,

He was actually wanting to select the used range from A5 to E and last row
in the used range. I really don't think he was going to select A5 before
running the code and I also think this is actually something to work in
conjunction with some of the stuff he had posted yesterday.

Either way... thanks for your input. It is always appreciated.

Mark

"Rick Rothstein (MVP - VB)" wrote in
message ...
While I recognize the OP has finished with this thread, I thought I would
add my comment for the archives at least. Considering the OP said he had
A5 set as the selected cell; assuming selecting a starting cell before
running the macro is how the OP always proceeds, then your Range-approach
solution can be shortened down even further to this one-liner...

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Rick


"Mark Ivey" wrote in message
...
Consider this....

If I fully understand your request, give this snippet a go...


Mark



LastRow = Range("A5").End(xlDown).ROW

Range("A5:E" & LastRow).Select









"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr

Unable to mark the range in VB and get application error- WHY
 

I must have missed his post yesterday (I was getting ready for an event in
the evening and, obviously, didn't log on afterwards). His statement "Active
cell is A5" is what led me to conclude he might be selecting a cell from
which to select downward. Of course, in re-reading the post, I see I
completely overlooked the "5 columns to the right" part (that is what
happens, I guess, when you try and fit in answering questions while getting
ready for something else<g).

Rick


"Mark Ivey" wrote in message
...
Rick,

He was actually wanting to select the used range from A5 to E and last row
in the used range. I really don't think he was going to select A5 before
running the code and I also think this is actually something to work in
conjunction with some of the stuff he had posted yesterday.

Either way... thanks for your input. It is always appreciated.

Mark

"Rick Rothstein (MVP - VB)" wrote in
message ...
While I recognize the OP has finished with this thread, I thought I would
add my comment for the archives at least. Considering the OP said he had
A5 set as the selected cell; assuming selecting a starting cell before
running the macro is how the OP always proceeds, then your Range-approach
solution can be shortened down even further to this one-liner...

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Rick


"Mark Ivey" wrote in message
...
Consider this....

If I fully understand your request, give this snippet a go...


Mark



LastRow = Range("A5").End(xlDown).ROW

Range("A5:E" & LastRow).Select









"CAPTGNVR" wrote in message
...
DEAR ALL

Active cell is A5. I need to select the range from 5th row to the last
populated cell which is obtained from xldown.row to 5 columns to the
right.

Public Sub LASTTROW()
LASTROW = ActiveCell.End(xlDown).Row
ActiveCell.Range(Cells(0, 0), Cells(LASTROW, 5)).Select
End Sub

What is wrong in this code to get run time error 1004-
'application-defined
or object defined error'.

or sugest VB to mark from the active cell to last populated row and 5
columns to the right.

pls help-captgnvr