Problem with For Loop

[alishehzad]

Dear Friends,

I am facing a strange problem. I used a code to process an Excel file.
The code has a part where it deletes the entire row with a blank entry
in any column. It is as follows:

****************** Code *****************
Del_Blank_Rows:

For J = 1 To 8
Columns(J).Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next J

Return
******************************************

PROBLEM :
The problem is that if I call this code from within
a loop. It gets stuck on some of the files and runs on some of them.
However, if i do not use the loop and repeat the code for each file,
then it works fine.

I have tried it with several files. It always gets stuck on the same
files. But processes the rest of them correctly. And when I dont call
it from a loop. It processes all the files correctly.

Please Help me Out !! Thank you in advance for your help.

 

[Guest]

As discussed in several threads, When you delete rows you need to loop from
the botom up
for J=8 to 1 step -1

 

[Guest]

OOps I looked at your code again ad you were selecting columns not rows.
Rereading I don't know what the problem is. That will teach me to reply too
fast.

 

[Bernie Deitrick]

Ali,

There is no need to loop:

On Error Resume Next
Columns("A:H").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

From your code, I can see no reason for the error that you describe. It could be something in your
other code, it could be events, it could be calculation.....

HTH,
Bernie
MS Excel MVP

 

[alishehzad]

Dear Friends, Thank you all for your help.

Firstly, Bernie the code you wrote with Columns("A:H").Cells. ....
Does NOT work. The snippet i am using works only if you select each
column one by one and then delete all the blank rows in it.

Secondly, what I have observed is that, it is not even about the Loop.
When I set the file names through a variable or an Array, it gets
stuck. However if I use the name directly it works fine.

If you people dont mind. I can paste both the codes. They are a little
long but I guess they will give the better picture.

Waiting for your reply on this strange issue !

Thanks a lot in advance !

 

[Bernie Deitrick]

Go ahead - post your code....

HTH,
Bernie
MS Excel MVP

 

[alishehzad]

Dear Friends,

I thank you all for your support. The code probably had some trivial
mistake which I could not figure out by debugging. So I wrote all the
code over again ( as it was not that long). This removed what ever bug
was causing this problem.

Im grateful to all of you for taking time to help me. And I'm thankful
to the moderators of this forum for providing us with the opportunity
to share our problems and find solutions.

Thanks again ...

Regards,

Ali