Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why would End(XLup) stop working??
XL2007 - the code imports values from another workbook. I then use
rw = wks2.Range("B250").End(XLup).Row to get the row number of the last value imported. Until this morning, it worked fine. Then I get the call: "I broke it!" He had an error with an invalid string and Excel crashed. He restarted Excel - and now End won't work! The values are imported and updated just fine. I even added in a Copy and PasteSpecial(xlvalues) just in case. But I can manually go to B250, hit CTRL+up arrow, and it blips through every imported and now copied/pasted value that was imported to stop at the header in row 1. Same thing the code is doing. Anyone else ever get this? Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why would End(XLup) stop working??
rw = wks2.Cells(wks.Rows.Count, 2).End(xlup).Row
-- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware .. .. .. "Ed from AZ" wrote in message ... XL2007 - the code imports values from another workbook. I then use rw = wks2.Range("B250").End(XLup).Row to get the row number of the last value imported. Until this morning, it worked fine. Then I get the call: "I broke it!" He had an error with an invalid string and Excel crashed. He restarted Excel - and now End won't work! The values are imported and updated just fine. I even added in a Copy and PasteSpecial(xlvalues) just in case. But I can manually go to B250, hit CTRL+up arrow, and it blips through every imported and now copied/pasted value that was imported to stop at the header in row 1. Same thing the code is doing. Anyone else ever get this? Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why would End(XLup) stop working??
Not disagreeing with you at all, Jim - just showing my ignorance --
what's the difference between using End(xlup) in your code and in mine? I've used that syntax in 'most everything for a few years now. And it worked fine in this macro - until the crash this morning. Is there something inherently suspect in my syntax that yours resolves? Ed On Nov 1, 11:04*am, "Jim Cone" wrote: rw = wks2.Cells(wks.Rows.Count, 2).End(xlup).Row -- Jim Cone Portland, Oregon *USAhttp://www.mediafire.com/PrimitiveSoftware . . . "Ed from AZ" wrote in ... XL2007 - the code imports values from another workbook. *I then use rw = wks2.Range("B250").End(XLup).Row to get the row number of the last value imported. Until this morning, it worked fine. *Then I get the call: "I broke it!" He had an error with an invalid string and Excel crashed. He restarted Excel - and now End won't work! The values are imported and updated just fine. I even added in a Copy and PasteSpecial(xlvalues) just in case. But I can manually go to B250, hit CTRL+up arrow, and it blips through every imported and now copied/pasted value that was imported to stop at the header in row 1. *Same thing the code is doing. Anyone else ever get this? Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why would End(XLup) stop working??
If your import fills the first 250 rows then your code will return row 1 not the last row with data.
You need some empty cells between Range("B250") and the "last cell" above it. Generally, the bottom cell in a column is the best place to look up from. I suspect that the "data" in B1 is causing the crash. '--- Jim Cone "Ed from AZ" wrote in message ... Not disagreeing with you at all, Jim - just showing my ignorance -- what's the difference between using End(xlup) in your code and in mine? I've used that syntax in 'most everything for a few years now. And it worked fine in this macro - until the crash this morning. Is there something inherently suspect in my syntax that yours resolves? Ed On Nov 1, 11:04 am, "Jim Cone" wrote: rw = wks2.Cells(wks2.Rows.Count, 2).End(xlup).Row -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware . . . "Ed from AZ" wrote in ... XL2007 - the code imports values from another workbook. I then use rw = wks2.Range("B250").End(XLup).Row to get the row number of the last value imported. Until this morning, it worked fine. Then I get the call: "I broke it!" He had an error with an invalid string and Excel crashed. He restarted Excel - and now End won't work! The values are imported and updated just fine. I even added in a Copy and PasteSpecial(xlvalues) just in case. But I can manually go to B250, hit CTRL+up arrow, and it blips through every imported and now copied/pasted value that was imported to stop at the header in row 1. Same thing the code is doing. Anyone else ever get this? Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why would End(XLup) stop working??
On Nov 1, 1:27*pm, "Jim Cone" wrote:
If your import fills the first 250 rows then your code will return row 1 not the last row with data. You need some empty cells between Range("B250") and the "last cell" above it. Hm, hadn't thought of that. I can't remember how the import is done. Ok - it's a QueryTable - and that's about all I know of it, 'cuz I pulled a "how to" off the Help or the NewsGroup or somewhere and it worked. So if the web page it accesses for data goes to, say, 500 rows on the import, even if the rows from 100 to 500 appear blank, doing End(xlup) from row 250 will zip up to the top?? If so, I can change this. And definitely remember it!! Ed "Ed from AZ" wrote in ... Not disagreeing with you at all, Jim - just showing my ignorance -- what's the difference between using End(xlup) in your code and in mine? I've used that syntax in 'most everything for a few years now. And it worked fine in this macro - until the crash this morning. Is there something inherently suspect in my syntax that yours resolves? Ed On Nov 1, 11:04 am, "Jim Cone" wrote: rw = wks2.Cells(wks2.Rows.Count, 2).End(xlup).Row -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware . . . "Ed from AZ" wrote in ... XL2007 - the code imports values from another workbook. I then use rw = wks2.Range("B250").End(XLup).Row to get the row number of the last value imported. Until this morning, it worked fine. Then I get the call: "I broke it!" He had an error with an invalid string and Excel crashed. He restarted Excel - and now End won't work! The values are imported and updated just fine. I even added in a Copy and PasteSpecial(xlvalues) just in case. But I can manually go to B250, hit CTRL+up arrow, and it blips through every imported and now copied/pasted value that was imported to stop at the header in row 1. Same thing the code is doing. Anyone else ever get this? Ed- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why would End(XLup) stop working??
I recommend cleaning of all imported data before trying to massage it in Excel.
Imported data can have spaces inserted willy-nilly, trailing minus signs, spaces that aren't spaces, gratuitous word wrap and carriage returns plus ???. -- Jim Cone Portland, Oregon USA http://tinyurl.com/ExtrasForXL (excel add-in with many extras) "Ed from AZ" wrote in message ... On Nov 1, 1:27 pm, "Jim Cone" wrote: If your import fills the first 250 rows then your code will return row 1 not the last row with data. You need some empty cells between Range("B250") and the "last cell" above it. Hm, hadn't thought of that. I can't remember how the import is done. Ok - it's a QueryTable - and that's about all I know of it, 'cuz I pulled a "how to" off the Help or the NewsGroup or somewhere and it worked. So if the web page it accesses for data goes to, say, 500 rows on the import, even if the rows from 100 to 500 appear blank, doing End(xlup) from row 250 will zip up to the top?? If so, I can change this. And definitely remember it!! Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xlUp Not Working | Excel Programming | |||
Macros Stop Working | Excel Programming | |||
Why does this stop excell from working | Excel Programming | |||
Formulae stop working | Excel Worksheet Functions | |||
My Excel stop working can any one help? | Setting up and Configuration of Excel |