Deleting non empty rows

[Jan Kronsell]

This code deletes the row, if the cell in column C IS empty.

Sub SletBlank()
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

I need it to do it, the other way around. Delete all rows, where the cell in
column C IS NOT empty. But there is no xlCellType for "Nonblank". So how do
I best do what I want.

Jan

 

[Norman Jones]

Hi Jan,

Try:

Sub Tester()
On Error Resume Next
With Range("C:C")
.SpecialCells(xlCellTypeConstants).Delete
.SpecialCells(xlCellTypeFormulas).Delete

On Error GoTo 0
End With

End Sub

 

[Norman Jones]

Hi Jan,

I forgot to extend to the entire row:

Public Sub Tester()
On Error Resume Next
With Range("C:C")
.SpecialCells(xlCellTypeConstants). _
EntireRow.Delete
.SpecialCells(xlCellTypeFormulas). _
EntireRow.Delete

On Error GoTo 0
End With

End Sub

 

[Jan Kronsell]

Hi Norman
Thank you. Your code deletes the contents of the cells, but changing it to


.SpecialCells(xlCellTypeConstants).EntireRow.Delete

and so on did the trick.

Jan