PDA

View Full Version : Solved: Delete row if default value



gimli
03-29-2011, 10:15 AM
Hi,

Im looking to delete a row if the first cell is the defaut value of an excel cell..not sure what the default value of a cell is..i think its blank. So if A1 is default value I want the whole row deleted...

thanks much


Option Explicit

Sub DeleteRow()
Dim rRow, Nrow As Long
Dim oneCell As Range
rRow = Range("A1").End(xlDown).Row

For Each oneCell In Range("a1", Cells(rRow, 1)).Cells
If Left(oneCell.Value, 1) = " " Then
oneCell.EntireRow.Delete
End If
Next
End Sub

BrianMH
03-29-2011, 11:08 AM
remove the space from " "

mdmackillop
03-29-2011, 11:21 AM
Deleting rows needs to be done from the bottom up, or all at once

Two methods

Option Explicit

Sub DeleteRow()
Dim rRow As Long
Dim r As Long
rRow = Cells(Rows.Count, 1).End(xlUp).Row

For r = rRow To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next
End Sub

Sub DeleteRow2()
Dim LCell As Range
Set LCell = Cells(Rows.Count, 1).End(xlUp)
Range(Cells(1, 1), LCell).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

gimli
03-30-2011, 05:54 AM
thanks a bunch!

:bow:

gimli
03-31-2011, 05:55 AM
MDMACK,

I know I marked thread solved because it did resolve my initial question...

I want to use the following to delete rows based on a criteria...basically your second option. I am using a formula in J1:J50 which returns a value of 1...any other value than 1 I want to delete the entire row. I am getting an error of mismatch type I think because it doesnt like the formula in the cell? So basically I want to check J1:J50 for a value of 1..if its not equal to 1 I want to delete the row.

thanks


Option Explicit

Sub DeleteRow()
Dim rRow As Long
Dim r As Long
rRow = Cells(Rows.Count, 1).End(xlUp).Row

For r = rRow To 1 Step -1
If Cells(r, 10) <> 1 Then Rows(r).Delete
Next
End Sub