.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

 

[DocAElstein]

Hi

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

... this is just a quick follow - up in passing that may help anyone Googling this Thread in the future, ( It helped answer the same question elsewhere ):
. Putting it into simple words:

. When I loop with a +ve step or “move forwards” or “go upwards” and then delete something, the next thing I wanted to consider slips down into the “hole” left by the deletion. So when I “move” on I go onto the what is actually the “next next “ and miss out what should have been the next as this thing has slipped into the “hole” at my present position where i deleted.

. Similarly “going forward” and deleting can cause nasty VBA errors, as VBA may look for the thing to consider in a for each or similar loop type situation.
. This can occur when VBA tries to consider something it thinks should be there as it has not considered it yet. ( VBA sometimes makes an internal memory at the start of a Loop of all things and where they are, and , at least partially, ignores any attempts by you to change variable associated with the Loop Control. If then in the Loop you purposely or unintentionally change variables, by, for example, "going out of step” in a forward Loop, you can confuse VBA if it looks for something being sure it should be there but it isn’t ( anymore ) !!

. Going or Looping “backwards” means the bits that are “behind me going upwards” slip down and fill in the hole I just made. All these bits have been considered already. The next thing i then consider going backwards is the true next thing i wanted to consider.
.
. Hope that somewhat layman explanation helps
Alan