Deleting blank records with macro

Occasional Contributor

I need some help with the attached macro.


I don't know how many records the files, on which I will be running this macro, will have. For that reason I wrote the code to auto-populate columns I (=DaysOpen) and N (=DaysToAnswered), with a formula, for a total of 32000 records (to be sure the formula will also populate for all records in larger files). Is it possible to only have the formulas populate for records that have data? At the end of the macro I'm trying to remove the unnecessary rows. But I'm having trouble removing records 9, 10, 17 and 18. As soon as I add the code below to my macro, the macro becomes extremely slow.


For r = 32000 To 2 Step -1
If Range("C" & r).Value = "" Then
Range("C" & r).EntireRow.Delete
End If
Next r




2 Replies
Hi Kristal,

Eliminating the loop will make the code much faster.
You can do that by using SpecialCells. I would also make the range dynamic by looking at the number of rows used for each file.
You can replace your loop with one line of code:

Range("C2:C" & UsedRange.Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete