VBA-Delete row w/o value

bloodybrit90

Board Regular
Joined
Jul 18, 2011
Messages
111
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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

Board Regular
Joined
Jul 18, 2011
Messages
111
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
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):

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,779
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top