Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reset the range of FOR loop | Excel Discussion (Misc queries) | |||
Range Validation property not working w/ Protected worksheet | Excel Programming | |||
x y scatter chart series ranges reset to x(range) = y(range) | Charts and Charting in Excel | |||
Range variable not being reset | Excel Programming | |||
Loop thru files in DIR, delete worksheets & reset worksheet Name property?? | Excel Programming |