Macro to remove entire row if null value exist in column C

**sarat47.dash** · 02-18-2013, 01:25 PM

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

Advance thanks for your help

**JieJenn** · 02-18-2013, 01:41 PM

Sub d()
With ActiveSheet
    .Columns("C").TextToColumns
    .Cells(1, 1).CurrentRegion.AutoFilter 3, "="
    
    If .Cells(Rows.Count, 1).End(xlUp).Row > 1 Then .Rows("2:" & .Cells(Rows.Count, 1).End(xlUp).Row).Delete
    
    .AutoFilterMode = False
End With
End Sub

**nathansav** · 02-18-2013, 01:42 PM

Public Sub RemoveBlanks()

Dim rng As Excel.Range
Dim rw As Excel.Range

Set rng = ThisWorkbook.Worksheets("Sheet2").Range("C:C")

For Each rw In rng.Rows

If rw.Cells(1, 1).Value = "" Or Asc(rw.Cells(1, 1)) = 13 Then
rw.Delete
End If

Next rw

End Sub

**arlu1201** · 02-18-2013, 01:44 PM

nathansav,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**mike7952** · 02-18-2013, 01:46 PM

another

Sub abc()
 Dim a, i As Long
 a = Range("a1").CurrentRegion
 
 Application.ScreenUpdating = False
 For i = UBound(a) To 2 Step -1
    If Not Len(WorksheetFunction.Clean(a(i, 3))) > 0 Then
        Rows(i).Delete
    End If
 Next
 Application.ScreenUpdating = True
End Sub

**sarat47.dash** · 02-18-2013, 01:51 PM

Dear nathansav & Jen,

Thanks for your quick response.

I have used the macro but still so many blank cells are there in column 'C'.

May I request you to check in my attached spreadsheet as I m still so many cells with null value