• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1705
  • Last Modified:

Excel vba - If cells in multiple columns are empty, delete row

Hello Experts!

Attached is a sample of the data I'm cleaning up.  I'll need to delete rows whenever the corresponding cells in Columns B:E are empty.  I have highlighted in yellow what satisfies that condition.  

I would very much appreciate your help with code to delete rows where the cells in B:E are empty.

Thank you!
0
xllvr
Asked:
xllvr
  • 3
  • 3
1 Solution
 
Haris DjulicCommented:
No attachment
0
 
xllvrAuthor Commented:
Hi Haris,

I don't see a post or an attachment here.  Were you intending to comment?
0
 
Haris DjulicCommented:
In your question you are saying " Attached is a sample of the data I'm cleaning up. " but no attachment is there...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
xllvrAuthor Commented:
Oh no!  So strange...I attached it but failed to hit upload earlier.  Apologies!

Here you go.
EE-Delete-Row-Test.xlsm
0
 
Haris DjulicCommented:
Hello,

here is the code:
Private Sub CommandButton1_Click()

i = 1
j = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Do While i < j
If i = 0 Then i = 1

If ActiveSheet.Range("B" & i).Value = "" And ActiveSheet.Range("C" & i).Value = "" And ActiveSheet.Range("D" & i).Value = "" And ActiveSheet.Range("E" & i).Value = "" Then
ActiveSheet.Rows(i).Delete
i = i - 1
j = j - 1
Else
i = i + 1
End If

Loop

End Sub

Open in new window


Attached is the sample file without empty lines.. Just replicate it to your copy of file...
EE-Delete-Row-Test.xlsm
0
 
xllvrAuthor Commented:
That worked beautifully!  Thanks so much for a great solution, Haris.  I'm so appreciative.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now