Delete record if cell is empty

Metrazal · #1 (**permalink**) 02-21-06, 10:02

I need the code to delete a record/row if cell Q (of that record/row) is empty.

What would be the best way to achieve this?

Thanks,

Met

Fazza · #2 (**permalink**) 02-21-06, 18:53

Hi Met,

Something like below is efficient - deletes all rows where blank anywhere in column "Q". Modify to suit your exact requirement.

regards,
Fazza

Code:

Columns("Q").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Metrazal · #3 (**permalink**) 02-22-06, 07:34

Same senerio except the cells are 0 values, not blank but zero values?

Thanks,
Met

Fazza · #4 (**permalink**) 02-22-06, 19:06

Hi Met,

Deleting records containing zeros is not quite as simple as deleting blanks.

And it depends on how many records there are.

Some thoughts.

1. Filter them (& blanks) out via SQL. This is really efficient. Preferably when creating the table. Or when creating a new table from your existing table.

2. For one row only, and it depends on how you've referred to the row. Something like

Code:

With Range("Q" & Your_Row_Number)
    If .Value = 0 Then .EntireRow.Delete
End With

3. If there aren't too many records, loop through all cells applying something like number 2 above each time. If looping, probably best to loop from the last record backwards to the first.

4. Without fully coding it. If there are many records, either filter the data (using Autofilter) on zeros and then using

Code:

'your range reference'.SpecialCells.xlCellTypeVisible.EntireRowDelete

or, temporarily add one extra column (only if you are not already at Excel's maximum of 256 columns) and have a formula the results of which you can test via SpecialCells to distinguish between the records you want and don't want. Again, "EntireRow.Delete". Then delete the temporary column.

Modify to suit, something like this code I use in my Excel personal macros.

Code:

Sub DeleteRowsWhereZeroInCurrentColumn()

    Application.ScreenUpdating = False
    
    Dim lCol As Long
    
    lCol = ActiveCell.Column
    
    'Enter new first column (avoids upsetting any filters); would fail if already using 256 columns.
    With Range(Cells(1, lCol), Cells(65536, lCol).End(xlUp)).Offset(0, 1 - lCol)
        .EntireColumn.Insert
        
        With .Offset(0, -1)
            .FormulaR1C1 = "=1/RC[" & lCol & "]"
            .Value = .Value
            .Range("A1").ClearContents
            
            On Error Resume Next
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
            Err.Clear
            
            .EntireColumn.Delete
        End With
    End With
    
End Sub

regards,
Fazza

DavidCoutts · #5 (**permalink**) 03-02-06, 05:01

or another way ( i use this quite often)

Code:

Sub removeZeroRows()
    Dim wks As Worksheet
    Dim rng As Range
    
    Set wks = ActiveSheet
    Set rng = Range("Q1:Q" & LastCell(wks).Row)
    
     
    With rng
        .Replace "", "DummyValue"   'fill blanks with dummy values
        .Replace "0", ""            'change value to replace with blanks
        On Error Resume Next
        'delete the cell values
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        If Err.Number <> 0 Then
            'gets here if no cells were found,
            Debug.Print Err.Number & " " & Err.Description
            Err.Clear       'remove the error
        End If
        
        On Error GoTo 0
    End With
    
    Set rng = Range("Q1:Q" & LastCell(wks).Row)
    rng.Replace "DummyValue", ""
    
    Set rng = Nothing
End Sub

'returns the range value of the lastcell
'if none exists return the cell A1
Function LastCell(wks As Worksheet) As Range
    Dim lCell As Range
    
    On Error GoTo errorHandler
    
    
    'find the last cell within the current worksheet
    Set lCell = wks.UsedRange.Find("*", , , , , xlPrevious)
    'determine if the lastcell is an actual value
    If Not lCell Is Nothing Then
        'set the value of the lastcell
        Set LastCell = lCell
    Else
        Set LastCell = Range("A1")
    End If
    
    'final tidy up
    Set lCell = Nothing
    
    Exit Function
errorHandler:
    Debug.Print Err.Number & " " & Err.Description
    

End Function

This is the way i use, but this is just to let you see another example of how to do this, As Fazza has already given you a good way of handaling this problem, the function which i use for lastcell is very useful in places.

All the best
Dave

Fazza · #6 (**permalink**) 03-06-06, 19:00

Thanks, Dave.

Using replace is neat.

It made be think further about the example I gave. The particular formula in the example divides a cell's content into 1. Resultant errors are deleted. This will therefore delete not just cells that were originally zero, but also those with text and those that originally contained errors. So, a little different from the specification, and potentially dangerous in that rows can be unexpectedly deleted. Hence, I have reposted. The formula could be change to overcome this: something like, =IF(cel=0,NA(),"")

regards,
Fazza

Internet Services

Web Development

Digital Marketing

Consumer Tech