[mso] Re: Excel 2002 - Range of Data Question
- From: "Glenda Wells" <gwells@xxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Thu, 3 Jun 2004 16:50:21 -0400
Hi Christine.
I have this problem ALL the time...quite frustrating.
As long as the rows and columns are contiguous you can do shift end to
get to the last active cell in rows then delete all below that and shift
right arrow to get to the last active cell in columns then delete all to
the right. It's what I do most often.
There is a function that identifies the last occupied cell in a
worksheet that I've used from time to time but not being very good at
VBA, haven't figured out how to use it always, on this MVP web-site.
http://www.beyondtechnology.com/geeks012.shtml
here's the code and the caveat at the end.
hope this helps. /g
Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%
' Error-handling is here in case there is not any
' data in the worksheet
On Error Resume Next
With ws
' Find the last real row
LastRow& =3D .Cells.Find(What:=3D"*", _
SearchDirection:=3DxlPrevious, _
SearchOrder:=3DxlByRows).Row
' Find the last real column
LastCol% =3D .Cells.Find(What:=3D"*", _
SearchDirection:=3DxlPrevious, _
SearchOrder:=3DxlByColumns).Column
End With
' Finally, initialize a Range object variable for
' the last populated row.
Set LastCell =3D ws.Cells(LastRow&, LastCol%)
End Function
Using this Function:
The LastCell function shown here would not be used in a worksheet, but
would be called from another VBA procedure. Implementing it is as simple
as the following example:
Example:
Sub Demo()
MsgBox LastCell(Sheet1).Row
End Sub
-----Original Message-----
From: McDonald, Christine, Ms, DCAA [mailto:Christine.McDonald@xxxxxxxx]
Sent: Thursday, June 03, 2004 04:17 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Excel 2002 - Range of Data Question
I have an Excel sheet that takes up rows 1-1206, columns A-Z. The
spreadsheet data was created in another program and exported to an Excel
file. When I do a ctrl-shift end or down arrow, it goes all the way to
the last row of Excel, 65???, even though there is no data. So I
highlighted rows 1207-65??? and deleted them, then I did the same thing
across to delete all the columns from row AA through the end. Even
doing this multiple times, Excel still thinks there is data all the way
to the last row. It makes it difficult to navigate through the data, as
a small movement of the scroll bar moves the cursor a long way through
the data. Also, I'm trying to create a macro, that needs to know where
the end of the data is.
Anyone know why I'm getting this behavior and if there is a way to get
Excel to recognize where the data actually ends?
Thanks,
Christine McDonald
Technical Specialist
Regional Webmaster
Western Regional Office
Information Technology Division (RSA-4)
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will
also allow you to change your email settings to digest or vacation (no
mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions. Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
_____ =20
<< ella for Spam Control >> has removed 10889 Spam messages and set
aside 0 Newsletters for me
You can use it too - and it's FREE! www.ellaforspam.com=09
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
