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

[CAPTGNVR]

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

 

[NateBuckley]

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.

 

[Mike H]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mark Ivey]

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]

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

 

[CAPTGNVR]

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

 

[CAPTGNVR]

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

 

[Chip Pearson]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mark Ivey]

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\)]

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