Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset UsedRange
Is there a functional difference between Debra Dalgleish's code for this at:
http://www.contextures.on.ca/xlfaqApp.html#Unused And this: Sub ResetUsedRange() Dim i As Integer, j As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count j = w.UsedRange.Columns.Count Next w End Sub I don't use UsedRange that often. When I have used it, both procedures give my identical results. Just curious. -- tj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset UsedRange
This seems to work as well. Will it miss something under some circumstances?
Sub ResetUsedRange() Dim i As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count Next w End Sub -- tj "tjtjjtjt" wrote: Is there a functional difference between Debra Dalgleish's code for this at: http://www.contextures.on.ca/xlfaqApp.html#Unused And this: Sub ResetUsedRange() Dim i As Integer, j As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count j = w.UsedRange.Columns.Count Next w End Sub I don't use UsedRange that often. When I have used it, both procedures give my identical results. Just curious. -- tj |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset UsedRange
usedrange is determined by both data, formats and comments. your code will reset the usedrange only if data was removed from cells with "standard" style/ general numbers Debra's code will remove remaining formatting after the last "data". -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam tjtjjtjt wrote : Is there a functional difference between Debra Dalgleish's code for this at: http://www.contextures.on.ca/xlfaqApp.html#Unused And this: Sub ResetUsedRange() Dim i As Integer, j As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count j = w.UsedRange.Columns.Count Next w End Sub I don't use UsedRange that often. When I have used it, both procedures give my identical results. Just curious. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset UsedRange
Thanks, that'sjust the type of thing I am curious about.
-- tj "keepITcool" wrote: usedrange is determined by both data, formats and comments. your code will reset the usedrange only if data was removed from cells with "standard" style/ general numbers Debra's code will remove remaining formatting after the last "data". -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam tjtjjtjt wrote : Is there a functional difference between Debra Dalgleish's code for this at: http://www.contextures.on.ca/xlfaqApp.html#Unused And this: Sub ResetUsedRange() Dim i As Integer, j As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count j = w.UsedRange.Columns.Count Next w End Sub I don't use UsedRange that often. When I have used it, both procedures give my identical results. Just curious. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset UsedRange
The second one is much faster, if you don't make other changes in your
workbook you will have to save the file as opposed to canceling out. The version I've used does not have the line you have to do the columns as well so it might be more reliable than the one I've been using. But if you want to be sure you complete the task properly you can use the one at Debra's site. which will take noticeably longer. If you know you have a problem I'd go with Debra's unless you've thoroughly checked the faster one. You can find the link to John Walkenbach's Tip 53 on my lastcell.htm page, to explain what is happening.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "tjtjjtjt" wrote in message ... Is there a functional difference between Debra Dalgleish's code for this at: http://www.contextures.on.ca/xlfaqApp.html#Unused And this: Sub ResetUsedRange() Dim i As Integer, j As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count j = w.UsedRange.Columns.Count Next w End Sub I don't use UsedRange that often. When I have used it, both procedures give my identical results. Just curious. -- tj |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reset UsedRange
Thanks for the information and the link.
-- tj "David McRitchie" wrote: The second one is much faster, if you don't make other changes in your workbook you will have to save the file as opposed to canceling out. The version I've used does not have the line you have to do the columns as well so it might be more reliable than the one I've been using. But if you want to be sure you complete the task properly you can use the one at Debra's site. which will take noticeably longer. If you know you have a problem I'd go with Debra's unless you've thoroughly checked the faster one. You can find the link to John Walkenbach's Tip 53 on my lastcell.htm page, to explain what is happening.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "tjtjjtjt" wrote in message ... Is there a functional difference between Debra Dalgleish's code for this at: http://www.contextures.on.ca/xlfaqApp.html#Unused And this: Sub ResetUsedRange() Dim i As Integer, j As Integer Dim w As Worksheet For Each w In ActiveWorkbook.Worksheets i = w.UsedRange.Rows.Count j = w.UsedRange.Columns.Count Next w End Sub I don't use UsedRange that often. When I have used it, both procedures give my identical results. Just curious. -- tj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
usedRange | Excel Programming | |||
usedrange | Excel Programming | |||
UsedRange | Excel Programming | |||
Help - How do I reset usedrange in excel 2000 | Excel Programming | |||
Usedrange | Excel Programming |