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

Sub SletBlank()
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).Enti reRow.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

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

---
Regards,
Norman


"Jan Kronsell" wrote in message
...
This code deletes the row, if the cell in column C IS empty.

Sub SletBlank()
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).Enti reRow.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

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


---
Regards,
Norman



"Norman Jones" wrote in message
...
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

---
Regards,
Norman


"Jan Kronsell" wrote in message
...
This code deletes the row, if the cell in column C IS empty.

Sub SletBlank()
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).Enti reRow.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

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


.SpecialCells(xlCellTypeConstants).EntireRow.Delet e

and so on did the trick.

Jan

"Norman Jones" skrev i en meddelelse
...
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

---
Regards,
Norman


"Jan Kronsell" wrote in message
...
This code deletes the row, if the cell in column C IS empty.

Sub SletBlank()
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).Enti reRow.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