0

Before thinking to flag this as a duplicate question, please let me assure you that I found not an answer satisfying this very simple need. So I have a picture included to help me describe what EXACTLY I want to do. Please scroll down and pass the picture. Just for the example

Imagine data being set in a neat way. Rows 1 to 9 must stay untouched. Rows 22 and so on must stay untouched.

From line 21 to 10 (that way intended) I want the code to check if the line is blank. If the line is blank I want the line deleted. Removed entirely. Otherwise, I want it untouched.

Thus in the picture, lines 1 to 9 stay untouched. From lines 21 to 10, only 10 and 11 stay. Lines 22 and so on stay untouched.

Checking the first cell if it is null, will do the trick, I just want the correct syntax to make it happen right and only within A21 to A10 for the example.

It is probably stupidly easy, but for some reason it gives me a hard time to do it right, since I am new to the VBA methods.

I am sure there will be the EntireRow.Delete method involved somehow I am just not so good yet with what you can do with VBA yet.

The first answer to this question was pretty near my needs and yet so far.

Excel VBA to delete blank rows within a range

Problem is that I am not very experienced and I don't understand all the methods, neither all arguments passed to that example. It is kinda complex and what I would love to see is something simple, documented in a way to understand what is going on, so I can implement it on my real life scenario.

This question: Excel VBA: Delete entire row if cell in column A is blank (Long Dataset)

The question has an answer for the ENTIRE spreadsheet, not helpful really in my case.

As you can see, I provide an example, I am not asking you to do my job, I just honestly wanna learn how to do what I asked.

I am not sure if I need a FOR that iterates with negative step -1, including some methods inside the loop, or if there is a ready method for this, including a looping, so here I am asking more experienced people how to do something, probably ridiculously easy.
Any help would be appreciated.

Community
  • 1
  • 1
George Eco
  • 466
  • 3
  • 16
  • 2
    `for x=numrows to 1 step -1:if range("a"&x).value="" then rows(x).delete:next x` – Nathan_Sav May 10 '17 at 14:44
  • Seriously??? Nathan if this works it is fantastic. Please compose an answer so I can accept it. Was it THAT simple? – George Eco May 10 '17 at 14:45
  • 1
    Modify the solution from the duplicate to operate against `Range("A10:A21")` instead of `Columns("A:A")`. – David Zemens May 10 '17 at 14:55
  • Oh! So David, then ("A:A") means the entire column A I suppose. Then making the Columns("A10:A21") Range("A10:A21") will just limit the effect to the specified range. Am I right? – George Eco May 10 '17 at 15:02

1 Answers1

1

You need to loop backwards from the end of the range and delete the relevant row index, like so

for x=numrows to 1 step -1
    if ws.range("a" & x).value="" then ws.rows(x).delete
next x

Where ws is the worksheet you are working on.

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • Thank you so much. Yes I assume I will set ws as my active worksheet before using that. So it was not EntireRow.delete after all. It was that! I will check it out after having a break, (I am pretty tired) but I am sure it will work thus I will accept the answer no matter what. – George Eco May 10 '17 at 14:58
  • 1
    @GeorgeEco `ws.Rows(x)` and `ws.Range("A" & x).EntireRow` refer to the same object/range. Both will do the same result. – David Zemens May 10 '17 at 14:59
  • Nathan, David I assume that if I need to check from row 21 to 10 I will just have to do a for x=21 to 10 step -1 right? – George Eco May 10 '17 at 15:15
  • It worked perfectly, after setting it on my task I needed it for. Thank you so much, both of you. @DavidZemens and Nathan – George Eco May 11 '17 at 23:11