Hi, i tried using for next loop to delete empty row. My code is as below
whenever i trigger the marco. My excel is like "hang". However, when i keep pressing ESC. It prompt the message saying the macro is being interupt. HOwever, then all my empty row has been deleted. What is wrong with my code?
Please Login or Register to view this content.
When deleting rows you should go backwards. If you go forwards, if you are on row 5 and you delete it, then the row that used to be row 6 is now row 5, and you are going to skip that and go to row 6. It looks like you are trying to manage that by subtracting 1 from the index, but that is not a good practice.
Try this. I would be happy to test it if you attach a file.
Please Login or Register to view this content.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Hello JP16,
To delete rows using a loop you need to work from the bottom up
Also, remove the counter (i = i - 1) as it is contradicting the Next i function.
So your code should look like this:-
I hope that this helps.Please Login or Register to view this content.
Cheerio,
vcoolio.
Ooops! Sorry Jeff. I've done it again! Please remove my post as its the same reply as yours.
Cheerio,
vcoolio.
No reason to remove it, and we don't remove posts just because they recommend the same solution. Even if it is the same solution it is good to have explanations from different angles. Great minds think alikeOriginally Posted by vcoolio
No worries Jeff. Its hard to tell who else is working on the same thread until post time.
Thanks again.
vcoolio.
Hi, please see attached. It will hang but eventually work if i press "ESC" to abort the code. why???
So if i wan I to decrease, i have to add a Step -1?
Anway, i thought my code consist of the i=i-1 which should address the problem u mention regarding deleting row?
The problem with your code is that you start with i=1, then with "i=i-1" i is now 0.
Then when the loop repeats with next i, the loop starts again with i=1, hence a never ending loop process.
Ya, i thot so too. But somehow, when i press ESC to abort. The end result is correct... funny....
I just noticed it, because the i=i-1 is in the if loop instead of the for loop, variable i will not -1 if the cell value is not blank. (you can see that for Cell(1,1) there is a value so it did not -1). Hence it will eventually get your answer but it will still stuck in a never ending loop
As I said, changing the index value inside a For loop is a poor programming practice. Did you try my version of the code?
Ya, i tried, work perfect! Thanks! Anyway, does the "step -1" mean the "i" decrease by 1 everytime right? Then can we make it step +2 to make "i" increase by 2 everytime?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks