Why would End(XLup) stop working??

[Ed from AZ]

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

 

[Jim Cone]

rw = wks2.Cells(wks.Rows.Count, 2).End(xlup).Row
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Ed from AZ" <[email protected]>
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

 

[Ed from AZ]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Ed from AZ]

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" aboveit.

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

 

[Jim Cone]

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

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