Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set the Display Alerts property of the application class | Excel Programming | |||
Hiding Rows Error - "Runtime Error 1004 - Unable to set the hidden property of the range class" | Excel Programming | |||
Unable to set the NumberFormat Property of the Range Class - ERROR | Excel Programming | |||
unable to close macro using auto_close or application.quit | Excel Programming | |||
unable to set formulaarray of range class ERROR when using Conditional Sum | Excel Programming |