?
Solved

Conditionally deleted row on empty string in cell

Posted on 2014-09-10
4
Medium Priority
?
99 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
[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
  • 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 52

Accepted Solution

by:
Rgonzo1971 earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

718 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