How to reset Used Range property of worksheet
 

Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536. When I
realized this was causing tiny slider bars and also causing unusually large
file sizes, I removed the code. I also selected all the unused rows (from
about row 300) and deleted them, then saved. This still didn't fix the tiny
slider bar or file size problem - but it should have, no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-

How to reset Used Range property of worksheet
 

http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536. When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused rows
(from about row 300) and deleted them, then saved. This still didn't fix
the tiny slider bar or file size problem - but it should have, no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-

How to reset Used Range property of worksheet
 

When I said I selected all the rows below 300, I meant the entire rows, and
I right-clicked and chose Delete. So yeah, I've tried that and it doesn't
work.

"Bob Phillips" wrote in message
...
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536. When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should have,
no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-

How to reset Used Range property of worksheet
 

There was another step in those instructions. Did you do that, too?

Maybe it'll work for you.

Sandusky wrote:

When I said I selected all the rows below 300, I meant the entire rows, and
I right-clicked and chose Delete. So yeah, I've tried that and it doesn't
work.

"Bob Phillips" wrote in message
...
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536. When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should have,
no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-






--

Dave Peterson

How to reset Used Range property of worksheet
 

I have deleted entire rows, saved, closed and reopened Excel - nada.

In my somewhat lengthy experience with Excel, deleting entire rows and
saving has always done the trick for me. No idea why it's not working now.
:(


"Dave Peterson" wrote in message
...
There was another step in those instructions. Did you do that, too?

Maybe it'll work for you.

Sandusky wrote:

When I said I selected all the rows below 300, I meant the entire rows,
and
I right-clicked and chose Delete. So yeah, I've tried that and it
doesn't
work.

"Bob Phillips" wrote in message
...
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536.
When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should
have,
no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-






--

Dave Peterson

How to reset Used Range property of worksheet
 

If you go to the VB Editor, type "activesheet.usedrange" in the
Immediate Window, then save/close/reopen your file, this should reset
the used range.

Also check out:
http://www.mvps.org/dmcritchie/excel/lastcell.htm

If it still doesn't work, you may want to try some code to see what
Excel is seeing. For example

Activesheet.usedrange.rows.count
Activesheet.usedrange.columns.count

will tell you how many rows/cols are "dirty".

HTH,
JP

On Nov 13, 3:58 pm, "Sandusky" wrote:
I have deleted entire rows, saved, closed and reopened Excel - nada.

In my somewhat lengthy experience with Excel, deleting entire rows and
saving has always done the trick for me. No idea why it's not working now.
:(

"Dave Peterson" wrote in message

...



There was another step in those instructions. Did you do that, too?

Maybe it'll work for you.

Sandusky wrote:

When I said I selected all the rows below 300, I meant the entire rows,
and
I right-clicked and chose Delete. So yeah, I've tried that and it
doesn't
work.

"Bob Phillips" wrote in message
.. .
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536.
When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should
have,
no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-

--

Dave Peterson- Hide quoted text -

- Show quoted text -

How to reset Used Range property of worksheet
 

I've seen that problem, too. Sometimes that last usedcell just won't let go.

I usually let excel win. But if it's really important, I'll copy what my
definition of the usedrange to a new worksheet (formulas, formatting) and names
and headers and...

And essentially start from scratch.

Sandusky wrote:

I have deleted entire rows, saved, closed and reopened Excel - nada.

In my somewhat lengthy experience with Excel, deleting entire rows and
saving has always done the trick for me. No idea why it's not working now.
:(

"Dave Peterson" wrote in message
...
There was another step in those instructions. Did you do that, too?

Maybe it'll work for you.

Sandusky wrote:

When I said I selected all the rows below 300, I meant the entire rows,
and
I right-clicked and chose Delete. So yeah, I've tried that and it
doesn't
work.

"Bob Phillips" wrote in message
...
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536.
When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should
have,
no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-






--

Dave Peterson

--

Dave Peterson

How to reset Used Range property of worksheet
 

I don't doubt what you are saying but that is truely odd. Just to confirm the
obvious... you do not have any code that might be creating the cells again
after the delete? You are actually deleteing the cells and not clearing the
contents? Here is code that I use to compact single sheets or entire
workbooks. You can give it a try if you wish...

Public Sub CompactAllSheets()
Dim wks As Worksheet

Application.ScreenUpdating = False
For Each wks In Worksheets
Call CompactSheet(wks)
Next wks
Application.ScreenUpdating = True

If MsgBox("For the compact to complete the spreadsheet must be saved. "
& _
"Do you want to save now?", vbYesNo + vbInformation, "Save?") = vbYes
Then wbk.Save
End Sub


Public Sub CompactSheet(Optional ByVal wks As Worksheet)
Dim rng As Range

If wks Is Nothing Then Set wks = ActiveSheet
Set rng = LastCell(wks)
wks.Range(rng.Offset(0, 1), wks.Cells(1,
Columns.Count)).EntireColumn.Delete
wks.Range(rng.Offset(1, 0), wks.Cells(Rows.Count, 1)).EntireRow.Delete

End Sub


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

--
HTH...

Jim Thomlinson


"Sandusky" wrote:

I have deleted entire rows, saved, closed and reopened Excel - nada.

In my somewhat lengthy experience with Excel, deleting entire rows and
saving has always done the trick for me. No idea why it's not working now.
:(


"Dave Peterson" wrote in message
...
There was another step in those instructions. Did you do that, too?

Maybe it'll work for you.

Sandusky wrote:

When I said I selected all the rows below 300, I meant the entire rows,
and
I right-clicked and chose Delete. So yeah, I've tried that and it
doesn't
work.

"Bob Phillips" wrote in message
...
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sandusky" wrote in message
...
Excel 2003 SP3
Winows XP Pro SP2

I wrote some [bad] code that was "hiding" a value in cell A65536.
When I
realized this was causing tiny slider bars and also causing unusually
large file sizes, I removed the code. I also selected all the unused
rows (from about row 300) and deleted them, then saved. This still
didn't fix the tiny slider bar or file size problem - but it should
have,
no?

Is there a way to reset the Used Range property of a worksheet?
Could something else be at play here?

Any and all help appreciated. Thanks!

-gk-






--

Dave Peterson