Hi

I have several sheets which are populated by macro's, using copy and paste.
i have found that with some sheets the scroll bar on left is small i.e Excel
thinks, the last row used is very high. e.g 6000
But the last row actually used is only 500 for example not 6000. This makes
my workbook size larger than necessary.

Is there a way to find the actual last row used i.e first blank cell in
coulmn A for each sheet and reset what Excell thinks is the last row. Hope i
am making sense !


Sunil

Have a look here

http://www.mvps.org/dmcritchie/excel/lastcell.htm

Mike

"sunilpatel" wrote:

Hi

I have several sheets which are populated by macro's, using copy and paste.
i have found that with some sheets the scroll bar on left is small i.e Excel
thinks, the last row used is very high. e.g 6000
But the last row actually used is only 500 for example not 6000. This makes
my workbook size larger than necessary.

Is there a way to find the actual last row used i.e first blank cell in
coulmn A for each sheet and reset what Excell thinks is the last row. Hope i
am making sense !


Sunil

Use

ActiveSheet.UsedRange.Select

This will normally reset the scroll bars. You may have to have screen
updating on, and you may have to close and re-open the workbook.

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"sunilpatel" wrote in message
...
Hi

I have several sheets which are populated by macro's, using copy and
paste.
i have found that with some sheets the scroll bar on left is small i.e
Excel thinks, the last row used is very high. e.g 6000
But the last row actually used is only 500 for example not 6000. This
makes my workbook size larger than necessary.

Is there a way to find the actual last row used i.e first blank cell in
coulmn A for each sheet and reset what Excell thinks is the last row. Hope
i am making sense !


Sunil

Use debra Dalgleish's reset all worksheets' usedrange code.

http://www.contextures.on.ca/xlfaqApp.html#Unused


Gord Dibben MS Excel MVP

On Sat, 2 Aug 2008 14:05:38 +0100, "sunilpatel"
wrote:

Hi

I have several sheets which are populated by macro's, using copy and paste.
i have found that with some sheets the scroll bar on left is small i.e Excel
thinks, the last row used is very high. e.g 6000
But the last row actually used is only 500 for example not 6000. This makes
my workbook size larger than necessary.

Is there a way to find the actual last row used i.e first blank cell in
coulmn A for each sheet and reset what Excell thinks is the last row. Hope i
am making sense !


Sunil

Ron Debruin has examples of using a function to return the last row
number that contains data:
http://www.rondebruin.nl/tips.htm

Cliff Edwards