Row numbers not updated after deleting entire rows

0
votes

I am making an Excel macro to filter things, and therefore delete unnecessary rows. First I create a range of irrelevant cells (by making the union of them), and after that I delete the whole row for the given range.

deleteRng.EntireRow.Delete

So if I have a worksheet with 100 rows and the rows from 40 to 60 are irrelevant, that range is deleted and the rows from 60 to 100 shifted up, so totally I got 80 rows counted from 1 to 80. This works perfectly most of time, but with some special workbooks the row numbers not updated after the deletion, therefore in the previous example the rows counted like 1...40,61...100.

SO33965610 question example

Is there a workbook related setting which do this or what else could be the problem?

1
excelvbarow
What is the deciding criteria that makes those workbooks special? Where are you reviewing the count of the rows? If you are seeing the rows on the same worksheet jump from 40 to 61 is it possible the rows are hidden. Can you provide a screenshot of what you are seeing? – nbayly
"What is the deciding criteria that makes those workbooks special?" - That's my question too. Formally same as others, I guess some setting could be different.. I see visually the gap(s) between the row numbers as this image shows. The value of these "hidden" rows are empty if I refer to them in code. – galso
That gap between the numbers means that the rows are hidden. You can show them by highlighting both the rows above and below (15 and 19 in your example) and right clicking and selecting Unhide. Are there perhaps hidden rows already there when you initiate your script? I think your question would be much enhanced if you included your full script. – nbayly
Thanks for the hidden row suggestion. I didn't realize that the worksheet has a few hidden rows because it was hard to tell from a few hundred rows and I thought that the deletion cause the problem. So I delete the hidden rows before running my filter script and it works fine. Thanks. – galso
Glad my ramblings hit the mark. Can you kindly mark my answer below as a correct answer so this question can be closed? Regards – nbayly

1 Answers

0
votes

That gap between the numbers means that the rows are hidden. You can show them by highlighting both the rows above and below (15 and 19 in your example) and right clicking and selecting Unhide. Are there perhaps hidden rows already there when you initiate your script?