Macro - Delete blank cells - problem

[Dhamo]

Hi


I need to delete all rows if if the cell in U column is blank.


Sub a()

Worksheets("Testing Execution Data").Select

For i = 15 To 100

If Cells(i, 21).Value = "" Then

Cells(i, 21).Select

Rows(ActiveCell.Row).Select

Selection.Delete Shift:=xlUp

End If

Next

End Sub


But it is missing few rows. I think the problem is with my loop. Can anybody point out where I should correct?


Note: I have formula in U column. (=if(a1="","","Valid")

 

[Debraj]

Hi Dhamo..


I have not tested the code.. but.. try to change

For i = 15 To 100 to

For i = 100 To 15 step -1

as when you start delete row 15, row 16 will take place of 15, and for loop will exclude row 16, as it was currently row 15, what you have already checked..


Confused... .. sorry.. but that's the actual problem.


Regards,

Deb

 

[Dhamo]

Thanks Deb. It works.! But my actual row range is 15 to 1652. It takes more than 3 mins to delete the rows. Is there any possibility to reduce the time?

 

[Dhamo]

Also I tried with this code.


Dim myColm As Range

Set myColm = Range("C:C")

myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub


As I already mentioned in my first post, i am using formulas and it is not deleting the rows which has formulas.

 

[Hui]

Dhamo

Try: Set myColm = Range("C1", Range("C1048576").End(xlUp))


That will reduce the calcs to the cells in Column C between C1 and the bottom of the data

 

[Marc L]

Hi !   Time is long 'cause of these awful Select !


Your code should be without any Select :  

If Cells(i, 21).Value = "" Then Rows(i).Delete 


Use at the beginning also  Application.ScreenUpdating = False

 your code will be faster …


Think, but think object ‼

 

[Dhamo]

Thanks Hui and Marc L. But still it is taking time. Any other solution you have?

 

[Marc L]

I would see after you post your "new code" …

 

[jason]

go grab a cup of coffee?