PDA

View Full Version : Solved: Simple delete entire row



Anomandaris
06-01-2009, 09:09 AM
I'm trying to delete entire row if cell in Column C is blank, but how do I say that the cell is blank, I tried 'Nothing', 'IsEmpty' but I'm doing it wrong

this is such an easy one, I cant even figure this out
any suggestions?
thanks


Sub deleter()
Dim i As Integer
Dim cell As Range
Dim rng As Range
i = Sheets("Metals").Range("C" & Rows.Count).End(xlUp).Row
Set rng = Sheets("Metals").Range("C4:C" & i)
For Each cell In rng
If cell.Value = xlCellTypeBlanks Then
cell.EntireRow.Delete
End If

Next cell
End Sub

MaximS
06-01-2009, 09:15 AM
try:


Sub deleter()
Dim i, j As Integer

i = Sheets("Metals").Range("C" & Rows.Count).End(xlUp).Row

For j = i To 4 Step - 1
If cells(j, 3).Value = "" Then
Rows(j).EntireRow.Delete
End If

Next j
End Sub

lucas
06-01-2009, 09:17 AM
Something like this. You might want to use rows.count instead of the hard coded number, ie 65536

Option Explicit
Sub DeleteBlankInColA()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
Range("A1").Select
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text Like "[]"
If test = True Then Cells(x, col).EntireRow.Delete
Next
End Sub

Paul_Hossler
06-01-2009, 09:28 AM
Maybe something like this



On Error Resume Next
With Worksheets("sheet1")
Intersect(.UsedRange, .Columns("C:C")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
On Error GoTo 0


Paul

mdmackillop
06-01-2009, 09:31 AM
Without looping. But this will not delete Row 1

Sub Macro1()
Columns("C:C").AutoFilter Field:=1, Criteria1:="="
Rows("2:" & Cells.Find("*", searchdirection:=xlPrevious).Row).Delete
Columns("C:C").AutoFilter
End Sub

Anomandaris
06-01-2009, 09:39 AM
thanks a lot guys

lucas
06-01-2009, 09:39 AM
or just:
Sub Macro1()
Columns("J").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub