Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    14

    Unanswered: Delete record if cell is empty

    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

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    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

  3. #3
    Join Date
    Feb 2006
    Posts
    14

    Ok, thanks.. One more question

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

    Thanks,
    Met

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    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

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  6. #6
    Join Date
    Feb 2006
    Posts
    113
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •