Solved

Conditionally deleted row on empty string in cell

Posted on 2014-09-10
4
92 Views
Last Modified: 2014-09-10
Dear Experts:

For the selection on the current worksheet (A70:E80) the following macro action has to be performed:

Check whether the cells in  A70:A80 have an empty string (the empty string stems from a formula) and if so, the whole row has to be deleted.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
0
Comment
Question by:AndreasHermle
  • 2
4 Comments
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 40314893
Initially I would ask; when you say delete the row do you mean make the rows values zero or remove the row moving all others up. The latter would change the size of the space you are working on.

Also are you working on a selection or an absolute range? Will additional cells be added to the worksheet, and if so will they also need to be processed?
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40314912
Hi,

pls try

Sub Macro()

Set myRange = Selection.Resize(Selection.Rows.Count, 1)
For Idx = myRange.Row + myRange.Rows.Count - 1 To myRange.Row Step -1
    If Cells(Idx, myRange.Column).Value = "" Then
        Cells(Idx, myRange.Column).EntireRow.Delete
    End If
Next

End Sub

Regards
0
 

Author Comment

by:AndreasHermle
ID: 40315002
Hi Russell,

I may have been a little vague in my questioning therefore your questions are justified.

nevertheless, Rgonzo macro works just great.

Thank you very much for your great help, anyways.
0
 

Author Closing Comment

by:AndreasHermle
ID: 40315007
As always great and swift coding from your side.

Thank you very much for it. Regards, Andreas
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now