EntireRow.Delete produces a Last Line index that reaches the max 1048576 !!

[Bastringue]

Hello,

I feel frustrated with the behavior of EntireRow.Delete. I show it in the short snippet below.

Simply, I populate a table with 1 line (by hand or with the button 'populate') : the line contains 3 values : "1" , "91" , a date.

Then, I try to erase the lines (I know there is only one ) that contain "91" in the second column...
This works well with the EntireRow.Delete method : Excel erases my unique line (and keeps the header line) and this is fine.

But, after deleting this line, I get Range('A1').End(xlDown).Row = 1048576 (the max!) and I don't understand why.

I was expecting this value to be "1" (I still have my line with the titles).

By the way, the value of Source.Cells(Source.Rows.Count, "A").End(xlUp).Row shows the correct value ("1") but I don't understand why Range('A1').End(xlDown).Row does not...

Range('A1').End(xlDown).Row does not count the nb of lines before the first blank cell in A ?

Thanks for any help, Pierre

Please Login or Register  to view this content.

[mcsythera]

You should replace Source.Range("A1").End(xlDown).Row with Source.Cells(Source.Rows.Count, "A").End(xlUp).Row. For whatever reason, Excel does not like searching down from a range and seemingly includes all the blank cells as part of the total number it finds.

[Bastringue]

Yes...indeed... and thanks for your answer....I found out this 'subtlety' after a couple of hours !! I am going now to change all the similar lines in my application ...