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

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

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.

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.

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

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