Delete row where specific cell is blank or meets condition

[hip2b2]

I have seen any number of macros that delete a blank row. What I am looking for is a macro that will delete a row when a specific condition is met on that roe, i.e. cell in column XX is blank, value in specific cell = x, etc.

Any suggestions?

Thanks

hip

 

[Craig1]

Sorry Guys,

The full code is:-

Range("K:K").SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.delete
Range("A1").Select 'Get back to top of Sheet
With ActiveSheet
'resets the last used cell.
.UsedRange
'Identify the last used row
lnLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

I missed out the bottom section when I copied it from the VBA script.

Again Thanks in advance
Craig.

 

[Craig1]

Any help on this would be appreiated. I have tried a few different options myself and cannot seem to get what I want!!!!

Hopefuly you experts will be able to sort me out.

Craig.

 

[mancubus]

hi,

i'm using the following to delete rows on condition...

If any cell in range A1:A5000 is a blank cell then delete entire row:

Sub delete_rows_blank()
 
On Error GoTo End
 
[A1:A5000].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
End:
 
End Sub

If the formula in any cell returns error then delete entire row:

Sub delete_rows_with_error_formula()
 
Dim sht As Worksheet
 
For Each sht In Worksheets
   Debug.Print sht.Name
   sht.Activate
   On Error Resume Next
   Cells.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Next sht
 
End Sub

If the formula in any cell returns ref error then delete entire row:
(sometimes above code does not work for ref errors. i donot know why. so i add the following to my project)

Sub delete_rows_with_ref_error()
 
Dim sht As Worksheet
Dim Rng
Dim CR
 
On Error Resume Next
Rng = sht.Range("A1:A5000")
CR = 1
For Each sht In Worksheets
   Debug.Print sht.Name
   sht.Activate
   For Each oCell In Rng
      Do
      If IsError(sht.Range("A" & CR)) Then
      Rows(CR & ":" & CR).Select
      Selection.Delete Shift:=xlUp
      End If
      Loop Until IsError(sht.Range("A" & CR)) = False
      CR = CR + 1
      Next oCell
Next sht
 
End Sub

If the value of any cell in column A is 0 then delete entire row:

Sub delete_rows_zero()
 
Dim nMaxRow As Long, nrow As Long
nMaxRow = ActiveSheet.UsedRange.Rows.Count
 
For nrow = nMaxRow To 1 Step -1
        If Range("A" & nrow).Value = 0 Then
        Range("A" & nrow).EntireRow.Delete
        End If
Next nrow
 
End Sub

Sub Example1()
'http://www.xtremevbtalk.com/showpost.php?p=1305670&postcount=4
 
    Const sTOFIND As String = "Hello"

    Dim rngFound As Range, rngToDelete As Range
    Dim sFirstAddress As String
    
    Application.ScreenUpdating = False
    
    With Sheet1.Range("A:A")
        Set rngFound = .Find( _
                            What:=sTOFIND, _
                            Lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=True)
        
        If Not rngFound Is Nothing Then
            Set rngToDelete = rngFound

            'note the address of the first found cell so we know where we started.
            sFirstAddress = rngFound.Address
            
            Set rngFound = .FindNext(After:=rngFound)
            
            Do Until rngFound.Address = sFirstAddress
                Set rngToDelete = Union(rngToDelete, rngFound)
                Set rngFound = .FindNext(After:=rngFound)
            Loop
        End If
    End With
    
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    
    Application.ScreenUpdating = True
End Sub

also:
http://www.xtremevbtalk.com/showthread.php?t=300757

 

[Craig1]

Thanks for the reply Mancubus.

I have now sorted out the deletion of the row based on an empty cell, thanks to you guys, but I'm struggling to work out how to delete the row if in column C the order number starts with a 1.
The majority of the order numbers start with a 5 and they are 8 digits long but we get the occasional few starting with a 1 which I do not need the info for and when I have 70 rows a time of info to go through i would prefer to remove the info I know I don't need.
I use a command button to extract the data from another workbook and it all works well now, except this last little hitch.

Any advice would be most appreciated.

Craig.

 

[julia55]

Re: Delete row where specific cell is blank or meets conditi

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub

I tried this one and like it because it allows me to select the column I want to look in, but it's not working for me. My sheet has data in the first 10 or so columns then has a bunch of blank rows before the next set of data. I am trying to delete those blank rows, but this is only deleting the rows with data and a 0 (no matter what I put for MyVal) in the column I input.

 

[JSmorada]

Re: Delete row where specific cell is blank or meets conditi

I'm trying to do something similar but with a date range. I have a spreadsheet with data imported from a SQL database. One of the columns (column G) has date values in it and I want to delete all the rows that have a date that is newer than, for example, 12/31/2011. I've done conditional formatting to make the color of all the cells with dates newer than 12/31/2011 and sorted the spreadsheet on column G to put those cells at top. I've just been deleting the rows manually by selecting them several pages at a time but it's still time consuming considering that there's several thousand rows that need to be deleted. I would like to find a way to automate this in order to save time.

I'm by no means an Excel guru and am doing this task only because it's a task that's been given to me...you know how that goes...any help would be greatly appreciated.

Thanks,
Jon

 

[Beemirza]

Re: Delete row where specific cell meets conditi i.e. No or yes Delete it

No, only looking for one condition to be met, ( most likley the if cel in col B, the delete row).

Thanks

hip

Try this one
cb_delete is press button on excel sheet that i created.

Private Sub cb_delete_Click()
Dim rr_del As Range
Dim i As Long
Dim l_delCount As Long

Dim s_data As String
l_delCount = 0
'Set aa = Range("A1:A51523")
Set rr_del = Range("A1:A500")
For i = 1 To 500
If IsError(Cells(i, 30)) = False Then
If Cells(i, 4).Value = "NO" Then
l_delCount = l_delCount + 1
'MsgBox "This Rec required to delete"
' rr_del.Rows(i).Delete
Range("NO" & i).EntireRow.Delete

End If
End If

Next i
MsgBox "Total No Record Deleted " & l_delCount
End Sub