UsedRange and Ctrl-Shift-End Mystery

[Guest]

PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A
(only) are populated with a variable amount of data -- could be three rows,
could be a thousand. For this discussion, let's say there are 50 rows.

When I select A1 and then hit Ctrl-Shift-End, I would expect A150 to be
selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting
them entirely to supposedly reset the used range, but get the same result
when I hit Ctrl-Shift-End.

To make it even more puzzling, if I copy the data from A150 into a new
worksheet in the same workbook, Ctrl-Shift-End works as expected.

And finally, I wrote a little code snippet to show me
ActiveWorkbook.Sheets("nameofsheet").UsedRange.Columns.Count, and it returns
4 in BOTH worksheets.

Who can suggest why this is happening (and ideally, a way to fix it
programmatically)? It's screwing me up when I try to import the weird
worksheet into MSAccess.

 

[Guest]

i just check a mock sheet trying selecting coulumns e:iv and going to format
and reseting to general.

 

[PCLIVE]

Select those empty rows again and go to Format-Conditional Formatting.
Select Delete and check all three condistions. Click OK. OK again.
Now try it.

Regards,
Paul

 

[Guest]

Nope, same result. I set E:IV cell format to General, but Ctrl-Shift-End
still selects A1:IV50.

 

[Guest]

You deleted the columns E through IV. You need to save the book however to
reset the used range. Note that the used range is not always what you would
think it is so you want to be careful using it in code... Here is some code
to find the bottom right most cell without refering to the used range...

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function

 

[Guest]

Thanks, Jim (and others who responded). My immediate problem is solved, but
Jim, your reply leads me off on a tangent. What does your function do that
UsedRange does not do -- i.e., in what situation will your function give a
right answer where UsedRange won't? Is this about UsedRange counting cells
that have been formatted even if they are blank?

 

[Guest]

The difference is that my function will alway return the last cell in the
range that contains values. It looks backwards from Cell A1 to find the last
used row with something in it and the last column with something in it. It
then creates a range object from that row and column address. Used range is
correct in most cases but not always.

What you think of as cells is really just a bunch of place holders that can
hold cell objects. Most of those objects have not been created. When you
place a value (or formula or???) in them then they get created. When you
delete the value (or ???) they usually get destroyed. If they don't get
destroyed for some reason then your used range is going to be wrong. By
deleteing the range with the errant cells you delete the object but don't
destroy it. For the destructor to run you need to save the sheet (depending
on your version of XL). I am not sure that this is the best explanation but
hopefully you get the jist of it...