Delete blank cells

Depends what you mean. Do you mean delete all null characters from cells (i.e. "" entries, perhaps as the result of formulas) so that they are 'truly' blank? Do you mean delete all rows which contain at least one blank cell? All columns?

Please be a bit more specific.

Regards

yes ,you are right. delete all null characters from cells (i.e. "" entries, perhaps as the result of formulas) so that they are 'truly' blank

Hi, joh46k,

Sub DeleteBlanks_1()
Dim lngCounter As Long

For lngCounter = Cells(Rows.Count, "F").End(xlUp).Row To 1 Step -1
  With Cells(lngCounter, "F")
    If .Value = "" Then
      .Value = .Value
    End If
  End With
Next lngCounter

End Sub

Sub DeleteBlanks_2()
Dim rngCell As Range

On Error Resume Next
For Each rngCell In Range("F:F").SpecialCells(xlCellTypeFormulas)
  With rngCell
    If .Value = "" Then
      .Value = .Value
    End If
  End With
Next rngCell
On Error GoTo 0

End Sub

Ciao,
Holger

Hi, joh46k,

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Arlette was kind enough to moderate 2 of your posts accordingly.

I donīt know what else in your workbook may be triggered as an event - maybe you should post a sample for that to have a look at. Both macros did fine in my test so itīs hard to tell why they should lead to an endless loop in your workbook (except for maybe events behind the sheet).

Ciao,
Holger

Hi, joh46k,

while being out for lunch I thought about your concern with

It is keep running not coming to stop

In the sample I developped I had only a small amount of data in one column and very few formulas all showing "" as a result. If you have made heavy use of formulas in the sheet the calculation after any altering might be a reason for the extreme long running time. Another one might be events that will get fired once any cell within a range gets altered (and they will get altered if you replace a formula with "").

Both reasons might be overcome by altering the first code to

Sub DeleteBlanks_1a()
Dim lngCounter As Long

With Application
  .Calculation = xlCalculationManual
  .EnableEvents = False
End With

For lngCounter = Cells(Rows.Count, "F").End(xlUp).Row To 1 Step -1
  With Cells(lngCounter, "F")
    If .Value = "" Then
      .Value = .Value
    End If
  End With
Next lngCounter

With Application
  .Calculation = xlAutomatic
  .EnableEvents = True
End With

End Sub

And the last reason for a long running time is when you have used formulas way beyond the used data range in preparation of the future. Both methods I had given you code for will not take care for the used data range but for the cells with formulae which are placed into Column F. If you have any other column which will be filled by hand or with solid data (meaning no formula) you may rely on that column instead of F to find the upper end of the range in which to work.

Ciao,
Holger