VBA to delete rows with certain conditions

[joelpe]

Hi,

I dont know how to create VBA macros and use that languaje, so I will ask here.

I have a sheet with 2 columns of text, A and B.

There are some rows with A blank and B with text, and others with text in A and B blank.

I need a VBA to delete those rows that have text in A but B is blank, while maintaining the other type (A blank and B with text) intact.

Many thanks

 

[Yongle]

Do you need VBA?

Can do it manually with Data Filter
Filter on column B = Blank
Delete all visible rows
Remove the filter and the remaining rows are visible again

 

[footoo]

Or you can create a macro with the macro recorder :
• Turn on the recorder
• Select column B
• Go to SpecialCells/Blanks
• Go to Edit/Delete/Rows
• Turn off the recorder

 

[Yongle]

or VBA solution

1 Test this in a COPY of your workbook
2 Paste some test data into sheet1 starting at cell A1
3 Add the code as detailed below
4 Run macro from sheet1 {ALT}{F8} lists available macros


Adding the code

Copy the code below \ {ALT}{F11} takes you to VBA \ {ALT} I M (Inserts a new Module) \ paste code there \ {ALT}{F11} takes you back to Excel

Sub FilterBlanksInColB()
    Dim rng1 As Range, rng2 As Range
    Set rng1 = ActiveSheet.Range("A1").CurrentRegion
    Set rng2 = rng1.Offset(1)
    On Error Resume Next
    rng1.Parent.ShowAllData
    rng1.AutoFilter Field:=2, Criteria1:="="
    rng2.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    rng1.Parent.ShowAllData
End Sub

 

[joelpe]

I didnt thught of using filters,that worked very well.

Thank you all!

 

[footoo]

Or :

Sub Delete_Blanks()
[B:B].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Yongle]

@joelpe Glad you solved your problem. Thanks for feedback
@footoo Short and simple. perfect