Column find code not working correctly

[Todd Huttenstine]

hey

The following code finds the last row used (variable
STACKEDUP) and find the last column used (variable
STACKEDLEFT). STACKEDUP works fine but STACKEDLEFT keeps
showing 256 which is column IV. There is no data in this
column so why does it keep finding it as the last column
with data in it. It should be finding column Z because
thats the last column that has data in it.


Worksheets("Location View").Select
STACKEDUP = Worksheets("Location View").Range("A65536").End
(xlUp).Row
STACKEDLEFT = Worksheets("Location View").Range("h:h").End
(xlToRight).Column


Thanks
Todd

 

[Doug Glancy]

Todd,

Try the analog to what you are doing with finding the last row:

STACKEDLEFT = Worksheets("Location View").Range("IV1").End(xlToLeft).Column

Note that this will find the last used column in Row 1 - adjust as needed.
If you need to find it for a whole range of rows, there have been some
discussions in this group you can google about using the Find method.

hth,

Doug Glancy

 

[Alan Beban]

Todd,

Try the analog to what you are doing with finding the last row:

STACKEDLEFT = Worksheets("Location View").Range("IV1").End(xlToLeft).Column

Note that this will find the last used column in Row 1 . . . .

Unless the last used column is Column IV. Slightly more general is,
with Worksheets("Location View") selected as you have shown,

STACKEDLEFT = Range("1:1").Find("*", , , , , xlPrevious).Column

Both will return an error message if the row is empty.

Alan Beban