Solved: Macro to remove entire if null value encounter in column

[sarat]

Hi,

How can I delete the entire rows if 'Null Values' are encountered in the cell of column C:C.
e.g. c15, c18,c29, c32-34 etc

[Bob Phillips]

'-----------------------------------------------------------------
Public Sub DeleteDuplicateRowsUsingAutofilter()
'-----------------------------------------------------------------
' Function: Conditionally remove rows
' Synopsis: Adds a worksheet function to test for uniqueness
' Applies Autofilter to results
' Deletes visible rows
' Note: Change the 'TEST_COLUMN' variable to the column to
' be checked for uniqueness
' Author: Bob Phillips - © 2008 xlDynamic.com
'-----------------------------------------------------------------
Const TEST_COLUMN As Long = 5
Dim rng As Range
Dim Lastrow As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Columns(TEST_COLUMN).Insert
.Cells(1, TEST_COLUMN).Value = "tmp"
.Cells(2, TEST_COLUMN).Resize(Lastrow - 1).Formula = "=LEN(SUBSTITUTE(C2,CHAR(10),""""))"
.Columns(TEST_COLUMN).NumberFormat = "0"

.Columns(TEST_COLUMN).AutoFilter
.UsedRange.AutoFilter Field:=TEST_COLUMN, Criteria1:=0, Operator:=xlAnd

Set rng = .Cells(2, TEST_COLUMN).Resize(Lastrow - 1)
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

.Columns(TEST_COLUMN).Delete
End With
End Sub