VBA-Delete row w/o value

[bloodybrit90]

I want to delete an entire row if there is info in column A and column C is blank.

Sub deleteBlank()
Dim Lastrow As Integer

Lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("C2:C" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


When I ran this, it said "no cells were found." I am new to VBA any help would be greatly appreciated.

Thanks,
Chris

 

[JLGWhiz]

Hi Chris, This procedure will do what you describe. If the cell in col C is blank and the cell in col A of the same row is not, it will delete the row. What it will not delete is a blank cell in col C when col A is also blank, or if col A is blank and col C is not.

Code:
Sub merge()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Set rng = sh.Range("C2:C" & lr)
For i = lr To 2 Step -1
If sh.Cells(i, 3) = "" And sh.Cells(i, 3).Offset(0, -2) > "" Then
Rows(i).Delete
End If
Next
End Sub
Code:

 

[AlphaFrog]

Your code looks good (functional). It worked for my quick test.

One question is; do you have truly blank cells in column C? If column C has formulas that return null strings "", those are not considered .SpecialCells(xlCellTypeBlanks).

 

[bloodybrit90]

Yes, I had a "" in the cell. I was able to use the code before any formulas and it worked. Thanks. How can I delete the row if column C has a value (not blank or "")

 

[Trebor76]

Hi bloodybrit90,

The following will delete an entire row if there are values in both columns A and C of that row (just note to initially try this on a copy of your data as the results cannot be undone):

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?640806-VBA-Delete-row-w-o-value
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    
    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Application.ScreenUpdating = False
    
    With Columns(lngMyCol)
        With Range(Cells(2, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(AND(LEN(A2)>0,LEN(C2)>0),""DEL"","""")"
            .Value = .Value
        End With
        .Replace "DEL", "#N/A", xlWhole
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert