.Clear or .Delete Shift:=xlUp (and Let y = y – 1) - UsedRange.Rows.Count anomale

[DocAElstein]

Hello.
I am having some problems with the difference between .Clear and , Delete. Or rather I had problems with .Clear which I have overcome with .Delete instead. But as I am learning I would like to understand why!!
. I do see for example from Thread http://www.mrexcel.com/forum/excel-questions/622612-difference-between-clear-delete.html that ..." .Delete deletes the row ".. And ...".Clear deletes the contents and formatting.. " , which I think I understand.

. But what I do not understand is the following:-
. After using Rows.Clear (where y as integer is the loop number of a loop) I found that I obtained (what I think? Is) the wrong result when testing the resulting size of a shortened File. (It returns the size of the original file!?)
. So after a bit of trial and error, I replaced

Rows(y).Clear

with

Rows(y).Delete Shift:=xlUp
  Let y = y – 1

and then got the results that I expected (The extra step Let y = y – 1 is necessary as otherwise in the next looping after deleting a row, a row would be missed out!). Amongst other things, the extra step makes the code more complicated, and I do not understand why .Clear does not appear to work, that is to say, why VBA still thinks that the emptied rows are part of the used range.
. Can anyone explain this to me?
. - Could it be, for example that .Clear does not, in fact, clear all formatting, so that VBA sees some form of formatting that is still there as “usage of the file”?
. - Is it necessary to include some other command, (that is to use a further property, method or whatever) which “re sets” the row to its default “empty” state? – (There, is for example, an awesome code from Iridium in Thread http://www.mrexcel.com/forum/excel-questions/73426-reset-last-cell-worksheet.html which may have that desired effect, but it takes extremely long (and in fact my Computer gives up at the .Delete part saying that there are just not enough resources to carry out such a massive operation…The Thread is from 2004, so it was probably based on Excel 2003 or earlier where there were much less cells to work through, and even if it did work, I would still like to know why the simple .Clear does not give the desired results.)

Thanks
Alan Elston.

P.s. If it helps I have prepared two simplified files to demonstrate the problem. (Excel 2007)
The first one demonstrates .Clear
Here is the file (in xlsm form and as a “zipped” file)

FileSnack | Easy file sharing
FileSnack | Easy file sharing

Here is The second one demonstrating .Delete (and y=y-1) file , (again as xlsm and as “Zipped” file)

FileSnack | Easy file sharing
FileSnack | Easy file sharing




In both files there is a macro in module “RecordVerwaltung” with the name “Sub ReorganizeRecordOrder()”. This shortens the row size of the file by doing some arbitrary reorganizing. .
Also in both files there is a macro in module “TestAndMrExcel” with the name “Sub ForMrExcelTestEndOfFile()” which tries to return the end of the file. For the first file it gives the same result both before and after shortened the file. For the second file it gives me a shortened value for the shortened file, as I expect. The only difference in the codes is that the first uses

Rows(y).Clear

and the second uses instead

Rows(y).Delete Shift:=xlUp
  Let y = y – 1

 

[Andrew Poulsom]

You should loop backwards when deleting rows rather than decrementing the counter when looping forwards. Example:

For y = 10 to 1 Step -1

 

[DocAElstein]

You should loop backwards when deleting rows rather than decrementing the counter when looping forwards. Example:

For y = 10 to 1 Step -1

Thanks for that.
When I think about it I should that realized that: By Going backwards I do not miss a step
I tried it ( and obviously it worked!). So that helps to simplify the .Delete File Macro.(I do not now need y = y – 1)
Alan

 

[DocAElstein]

.......[h=2]Re: .Clear or .Delete Shift:=xlUp (and Let y = y – 1) - UsedRange.Rows.Count anomale[/h]
I have come up with the following answer myself in the meantime by Goggling and experimenting.
How does this sound?

.1 After .Delete should always get expected results because the rows are no longer there. So there is no ambiguity.
.2a. After .Clear you may get the expected result provided that you have no format change. Depending on what version of Excel you have to save the file or save, close and open the file to get things reset.
.2b. If you do have format change then select the entire sheet and go through the formats giving a uniform format to the entire sheet. Then things should behave as in .2a.
.3 If, after using .Clear you want to find the end of your file there are two possibilities. If you are only interested in data or formulas in cells then this command is sufficient:
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If you want to check for any Format change then use this as well:
ActiveSheet.UsedRange.Rows.Count
If you get different results in using these two for the same file it is an indication of a format change somewhere. (This could be anything, even something not immediately noticeable when no data or formula is there, such as Font size etc)

I have 3 Follow-up Questions
.1 Any opinions on whether I have got it (at least approximately!) right?
.2 How can I change ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row to give me the lowest down cell with data in it over the entire sheet (other than looping through every cell in the sheet !)
.3 Does anyone know if .End(xlUp) would be described as a Method or a Property? (F1 and F2 are a bit vague on this one (at least in my current Xl 2007 and 2010))

Thanks
Alan.

 

[DocAElstein]

..Re: .Clear or .Delete Shift:=xlUp (and Let y = y – 1) - UsedRange.Rows.Count anomale


Bumpety-

.....
I have come up with the following answer myself in the meantime by Goggling and experimenting.
How does this sound?

.1 After .Delete should always get expected results because the rows are no longer there. So there is no ambiguity.
.2a. After .Clear you may get the expected result provided that you have no format change. Depending on what version of Excel you have to save the file or save, close and open the file to get things reset.
.2b. If you do have format change then select the entire sheet and go through the formats giving a uniform format to the entire sheet. Then things should behave as in .2a.
.3 If, after using .Clear you want to find the end of your file there are two possibilities. If you are only interested in data or formulas in cells then this command is sufficient:
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If you want to check for any Format change then use this as well:
ActiveSheet.UsedRange.Rows.Count
If you get different results in using these two for the same file it is an indication of a format change somewhere. (This could be anything, even something not immediately noticeable when no data or formula is there, such as Font size etc) …...

….BUMP!

.....
I have 3 Follow-up Questions
.1 Any opinions on whether I have got it (at least approximately!) right?
.2 How can I change ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row to give me the lowest down cell with data in it over the entire sheet (other than looping through every cell in the sheet !)
.3 Does anyone know if .End(xlUp) would be described as a Method or a Property? (F1 and F2 are a bit vague on this one (at least in my current Xl 2007 and 2010))……..

Any answers, even a Yes or No to 1. – If I have got roughly the right idea?


Thanks
Alan Elston.

 

[DocAElstein]

..Bumpety-


….BUMP!



Any answers, even a Yes or No to 1. – If I have got roughly the right idea?


Thanks
Alan Elston.

Bump Bump!

 

[shg]

1. I don't follow any of that.

2.

Sub demo()
    MsgBox Last(Worksheets("Sheet1")).Address(False, False)
End Sub

Function Last(wks As Worksheet) As Range
    Set Last = wks.Cells.Find(What:="*", _
                              After:=wks.Cells(1), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious)
End Function

3. From Help:

Range.End Property

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only Range object.

 

[DocAElstein]

Hi shg,
. Thanks for the Reply. - Sorry I was not too clear ( 1.) with my suggested answer to my original Thread!

……..

2.

Sub demo()
    MsgBox Last(Worksheets("Sheet1")).Address(False, False)
End Sub

Function Last(wks As Worksheet) As Range
    Set Last = wks.Cells.Find(What:="*", _
                              After:=wks.Cells(1), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious)
End Function

…..

….Super Code. Works great. Thanks

…3. From Help:

Range.End Property
Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only Range object.

… Thanks, I did not find the help as I did not to think to look at Range.End – Lack of experience, - yeah of course should have clicked by me that it is a Range property

Thanks again, I’m very grateful.
Alan Elston.

 

[shg]

You're welcome.

I did not find the help as I did not to think to look at Range.End

I sympathize; it's an unfortunate aspect of Help that you have to know what you're looking for to find it, and it's gotten dramatically worse in every version after Excel 2003. But in general, you should look for object.thing in Help, where object is what the thing applies to.

 

[DocAElstein]

.. Thanks, I did not find the help as I did not to think to look at Range.End – Lack of experience...

….. Generally, you should look for object.thing in Help, where object is what the thing applies to…..

. Good Tip…….

…… unfortunate aspect of Help that you have to know what you're looking …… it's gotten dramatically worse in every version after Excel 2003……...

. As a beginner I lack the experience but I have heard it said now a few times by experienced users u.a. Andrew poulsom, that the help Function is worse in newer versions, (Or maybe just not “keeping-up”??) – maybe a further contribution to that Thread about XL 2016 ( http://www.mrexcel.com/forum/lounge...ould-like-see-excel-2016-a-9.html#post3923216 ) could be “Get the Help Function in order”!

. Alan
P.s. I had an idea of standardizing to 2003 as I have a messy mixture of computers and XL 2003, 2007, 2010,, and then using VBA to get any new features. Unfortunately I hit a in getting all the extra colors from ab 2007 in 2003. - I have a thing with using lots of colors. (But maybe I should try to see a Psychiatrist about that!!)