Delete code

  • Thread starter Thread starter LiAD
  • Start date Start date
L

LiAD

Hi,

Could some give me a code please to delete the entrie row if any cell in col
C of that row starting from c10 going to the last cell down = ""?

Thanks
LiAD
 
This should work for you. Just place this code into a standard module and
adjust the name on the worksheet you want to use.

Option Explicit

Sub DeleteRows()

Dim lngLastRow As Long
Dim i As Long

With Sheets("Sheet1")

lngLastRow = .Cells(Rows.Count, "C").End(xlUp).Row

For i = lngLastRow To 10 Step -1
If Cells(i, "C").Value = "" Then
Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i
End With

End Sub

Hope this helps! If so, click "YES" below.
 
Correction,

Option Explicit

Sub DeleteRows()

Dim lngLastRow As Long
Dim i As Long

With Sheets("Sheet1")

lngLastRow = .Cells(Rows.Count, "C").End(xlUp).Row

For i = lngLastRow To 10 Step -1
If .Cells(i, "C").Value = "" Then
.Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i
End With

End Sub
 
Sub delSome()
Dim lr As Long, rng As Range
lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = ActiveSheet.Range("C10:C" & lr)
For Each c In rng
If c.Value = "" Then
c.EntireRow.Delete
End If
Next
End Sub
 
Sorry, delete should go from bottom up.

Sub delSome()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
'Set rng = ActiveSheet.Range("C10:C" & lr)
For i = lr To 10 Step - 1
If Cells(i, 3) = "" Then
Cells(i, 3).EntireRow.Delete
End If
Next
End Sub
 
Thanks

Ryan H said:
Correction,

Option Explicit

Sub DeleteRows()

Dim lngLastRow As Long
Dim i As Long

With Sheets("Sheet1")

lngLastRow = .Cells(Rows.Count, "C").End(xlUp).Row

For i = lngLastRow To 10 Step -1
If .Cells(i, "C").Value = "" Then
.Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i
End With

End Sub
Click to expand...
 
Assuming your cells in Column C have data in them so that your ="" condition
means an empty cell, and not formulas that evaluate to "", then give this
"non looping" macro a try...

Sub DeleteEmptyCellsColumnC()
On Error Resume Next
Range("C10:C" & Cells(Rows.Count, "C").End(xlUp).Row). _
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
 
Back
Top