VBA: deleting rows returned blank by a function

[MaryBrenna]

Hello everyone,

is there any possible way to set up a macro that will delete rows that were returned as blank from a function? I am putting together a book database and I want to have separate sheets for languages, genres etc. I decided to create a "General" sheet for all the info and then use links so that I only need to put the new information once.

To show only the selected books on a sheet, I used the if function as follows, so the name of the book only appears if it's in English. (And I have used this one for columns A-F to display all the info about the book as well.)
=IF(General!$D2="English";General!A2;"")

Now how could I automatically delete the rows which are returned blank by this function? I have tried a macro for deleting blank rows, but since these are not truly empty, it did not work. Or should I put the if function in a different way first?

Thank you all for your answers

---

Using Excel 2010 on Win7

 

[jomili]

Mary,

The easiest way would be to change your formula to

=IF(General!$D2="English",General!A2,"Delete")

Then you could search for "Delete" with your existing macro and delete those rows. The macro below will delete the rows containing the "Delete" value (just change "1) to whatever column contains that value.

Sub DeleteRowsBasedOnCriteria()</SPAN></SPAN>
 
'Assumes the list has a heading.</SPAN>
      With ActiveSheet</SPAN></SPAN>
             If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter</SPAN></SPAN>
                    .Range("A1").AutoFilter Field:=1, Criteria1:="Delete"</SPAN></SPAN>
                    .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _</SPAN></SPAN>
                  (xlCellTypeVisible).EntireRow.Delete</SPAN></SPAN>
            .AutoFilterMode = False</SPAN></SPAN>
      End With</SPAN></SPAN>
 
End Sub</SPAN></SPAN>

 

[AlphaFrog]

You could put this in an empty column
=General!$D2="English"

It will return TRUE\FALSE

Then select that column and AutoFilter for FALSE to hide all the TRUE rows.
Select all the visible rows and delete
Then turn off the autofilter.

 

[johnbird1988]

Please can you try this?

Sub BlankMe()
For Each c In ActiveSheet.Range("A5:A14")
If c.Value = "" Then c.Resize(1).EntireRow.Delete
Next c
End Sub

 

[MaryBrenna]

Mary,

The easiest way would be to change your formula to

=IF(General!$D2="English",General!A2,"Delete")

Then you could search for "Delete" with your existing macro and delete those rows.

that´s nice, should have figured out to get around it with text. thanks for including the macro

Please can you try this?

Sub BlankMe()
For Each c In ActiveSheet.Range("A5:A14")
If c.Value = "" Then c.Resize(1).EntireRow.Delete
Next c
End Sub

I´m not sure where the glitch is, it did delete some of the rows, but not all of them. That is, I had to run it like 5 times to get rid of every blank row. (And just to be clear, I did adjust the range right the first time, I really just re-run it again). I´ll put together some random stuff tomorrow to test it further and see if it happens again with a wholly new file. Has this ever happend to you?

But I love the idea, it will be absolutely the thing I was looking for once I see through the problem



Thank you all, any way it turns out, I´ll be able to have everything in order

 

[AlphaFrog]

When you delete a row, the rows below shift up. Then if you delete the next row below, you just skipped over the row that shifted up.

You need to delete from the bottom up.

[color=darkblue]Sub[/color] BlankMe()
    [color=darkblue]For[/color] i = 14 [color=darkblue]To[/color] 5 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Range("A" & i).Value = "" [color=darkblue]Then[/color] Rows(i).Delete
    [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[mirabeau]

When you delete a row, the rows below shift up. Then if you delete the next row below, you just skipped over the row that shifted up.

You need to delete from the bottom up.
...

You don't need to delete from the bottom up. That's only one of a number of options.

An example of deleting up not being useful is if you had a blank A15 with non-blank entries elsewhere in Row15. Whence you'd still have a result that the "deleting up" code was supposed to delete.

The code below is an example of an approach that doesn't delete from the bottom up and doesn't have the problem of the preceding paragraph. But as noted there's a variety of ways ...

Sub blanksinA()
Dim k As Long, rw
For Each rw In Rows("5:14")
If rw.Resize(, 1) = "" Then
    k = k + 1
    rw.ClearContents
Else
    If k > 0 Then rw.Cut rw.Offset(-k)
End If
Next
End Sub

 

[AlphaFrog]

You don't need to delete from the bottom up.

The use of need was merely explaining why the earlier code that deleted rows from the top down was skipping some rows. It wasn't intended to express that this was the only solution.

 

[MaryBrenna]

Thanks, both options worked perfectly. Didn't realize about skipping the rows

 

[jomili]

Mary,
Did the macro I posted at the front of the thread not work to delete the rows? In my experience, deleting by using the autofilter is the fastest method, MUCH faster than a row by row option.