Simon

Wouldn't CTRL + End work as well as your macro?


Gord Dibben MS Excel MVP

On Wed, 27 May 2009 08:52:23 +0100, Simon Lloyd
wrote:


Bloo;358963 Wrote:
Hi,
Could someone help me, for some reason i couldn't find any info on how
to
change the number of rows in a worksheet.
for ex: someone sent me a worksheet with 65526 rows in it, how can i
change
that to 1000 rows only...(the datas only up to the 800th row)

Many Thanks!As far as i know you can't!, Excel 97 - 2003 has 65536 rows, but your
workbook will only be the size of the data, that is to say if you have
data up to row 100 your workbook size isn't automatically bloated to the
last row and seen as data content, the best way to see what has/had data
in it do this:


Code:
--------------------

Sub MyUsedRange()
activsheet.usedrange.select
msgbox "The used range is " & Activesheet.UsedRange.Address
End Sub

--------------------

*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.