Solved

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

Posted on 2014-11-06
6
1,464 Views
Last Modified: 2014-11-06
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
Comment
Question by:xllvr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40427641
No attachment
0
 
LVL 1

Author Comment

by:xllvr
ID: 40427643
Hi Haris,

I don't see a post or an attachment here.  Were you intending to comment?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40427657
In your question you are saying " Attached is a sample of the data I'm cleaning up. " but no attachment is there...
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Comment

by:xllvr
ID: 40427661
Oh no!  So strange...I attached it but failed to hit upload earlier.  Apologies!

Here you go.
EE-Delete-Row-Test.xlsm
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40427671
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
 
LVL 1

Author Closing Comment

by:xllvr
ID: 40427675
That worked beautifully!  Thanks so much for a great solution, Haris.  I'm so appreciative.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question