Delete row if cell is empty

[Amanda1]

Hi

I need help - again.

Been searching and can't quite get the answer I need, for you it will be easy.

Worksheet - cols A through to E are populated, about +-2500 rows. Number of rows will vary whenever data is refreshed.

Requirement - on each row, if Cell E is blank, I would like to delete the complete row. This is a once only function at the end of working on the sheet.

I've got it to delete one row, but then it just stops.

I'm doing something wrong again - pls give me some hints.

Thanks & look after yourselves.

Amanda :bike:

[shades]

You can use code to do it:



Sub DeleteEmptyRowsMain()
'allows user to choose the column by selecting it.
Dim myColm As Range
Set myColm = Application.InputBox("Choose column(s) to clear", Type:=8)
On Error Resume Next
myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

[Bob Phillips]

Sub test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "E").Value = "" Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then
rng.Delete
End If

Set rng = Nothing

End Sub

[Amanda1]

Hi Gentlemen,

Rich, thank you, but it isn't quite what I need for this problem, (I'm keeping your script though because I think I have a use for that).

XLD, (pls put your name on, us "oldies" like addressing people properly). I have run your script, only for it to delete all the rows where there is nothing in Col E. Then I paniced because that is exactly what I didn't want it to do!! :drop:

There is nothing wrong with your script - it is my fault!:doh: I got slightly mixed up when I wrote my requirement & managed to miss out the word "not" before the blank. (Brain going a little quicker than the fingers).

I've never seen this "Union" that you have in your code and I have no idea what it does etc, so I'm reluctant to make changes on my own. What do I need to change around for the script to keep the rows where the cell in Col E are blank.

Thanks for your guidance & take care

Amanda. By the way, here was my novice effort, (it failed).



With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 2000
For Lrow = EndRow To StartRow Step -1
If (.Cells(Lrow, "E").Value) Then .Rows(Lrow).Delete
Else
End If

[Shazam]

SHAZAM!!!


Sub Delete_Rows()
Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

[Amanda1]

Hi

This script deletes the rows where Col E is blank - it must keep those rows and delete where Col E is not blank.

Cheers

Amanda

[shades]

This might prove too slow, but it does work:



Sub DeleteTest()
Dim c As Range
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, 5).End(xlUp).Row
For Each c In Range("E1:E" & LastRow)
If c.Value <> "" Then
c.EntireRow.Delete
End If
Next c
End Sub

[Zack Barresse]

SHAZAM!!!


Sub Delete_Rows()
Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

ROFL!! :funnyashe

Amanda, try changing the xlCellType to constants.

[mvidas]

Amanda,

For xld's answer (btw - xld = Bob), you would probably only have to change the ' = "" ' portion to ' <> "" ' to get it to work. Rich's last one wouldn't work if there were two non-blanks in a row. I'm not sure if Zack's suggestion would work for formulas (I didn't test it :P). Another way would be Sub amandadelete()
If Intersect(ActiveSheet.UsedRange, Columns("E")) Is Nothing Then Exit Sub
Dim CLL As Range, BadRows() As Long, Cnt As Long
For Each CLL In Intersect(ActiveSheet.UsedRange, Columns("E")).Cells
If Len(Trim$(CLL.Text)) > 0 Then
ReDim Preserve BadRows(Cnt)
BadRows(Cnt) = CLL.Row
Cnt = Cnt + 1
End If
Next
Application.ScreenUpdating = False
For Cnt = UBound(BadRows) To 0 Step -1
Rows(BadRows(Cnt)).Delete
Next
Application.ScreenUpdating = True
End SubI try and stay away from using Union in something like this, only in cases where there would be many many many rows to delete, as once union gets a certain amount of non-contiguous ranges in it, it takes a while to add. But for your 2500 rows it shouldn't really make a difference, though I'd be curious to see what works best out of everything here.
Also, way to live up to your name, shazam! I should start my posts with "MVIDAS!", though it wouldn't have nearly the same effect :)
Matt

[shades]

Amanda,

For xld's answer (btw - xld = Bob), you would probably only have to change the ' = "" ' portion to ' <> "" ' to get it to work. Rich's last one wouldn't work if there were two non-blanks in a row. I'm not sure if Zack's suggestion would work for formulas (I didn't test it :P).
Matt

Interesting, Matt. It would have to be run again for however many non-blank cells were contiguous. But it does work. ;)

[Bob Phillips]

I try and stay away from using Union in something like this, only in cases where there would be many many many rows to delete, as once union gets a certain amount of non-contiguous ranges in it, it takes a while to add.

You want fast?


Sub DeleteWhereNotBlank()
Dim rng As Range
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(1).Insert
Range("E1").Value = "Temp"
Set rng = Range("A2").Resize(iLastRow - 1)
Columns("E:E").AutoFilter Field:=1, Criteria1:="<>"
Set rng = rng.SpecialCells(xlCellTypeVisible).EntireRow
rng.Delete
Rows(1).Delete
End Sub

[Shazam]

Hi

I need help - again.

Been searching and can't quite get the answer I need, for you it will be easy.

Worksheet - cols A through to E are populated, about +-2500 rows. Number of rows will vary whenever data is refreshed.

Requirement - on each row, if Cell E is blank, I would like to delete the complete row. This is a once only function at the end of working on the sheet.

I've got it to delete one row, but then it just stops.

I'm doing something wrong again - pls give me some hints.

Thanks & look after yourselves.

Amanda :bike:



Are you saying if any cells in column E are blank delete the entire row. Right ?

[Norie]

Amanda

In your first post you said you wanted to delete rows where E was blank, in subsequent posts you say you want to delete rows where E is not blank.

Which is it?

[Bob Phillips]

Amanda

In your first post you said you wanted to delete rows where E was blank, in subsequent posts you say you want to delete rows where E is not blank.

Which is it?

Not blank, she explained the first post was in error.

[Norie]

xld

Sorry about that, must have not read that post fully.:oops:

[Bob Phillips]

xld

Sorry about that, must have not read that post fully.:oops:

I did, because I was told that my offering deleted exactly the wrong ones http://vbaexpress.com/forum/images/smilies/001.gif

[Shazam]

Not blank, she explained the first post was in error.

Hi XLD,


I'm curios if AMANDA1 wants to keep the blanks cell in colunm E and delete the cells that has vaules "Delete Entire Row" then the whole sheet will be blank. Is that what I'm understand ? If so why do it that way ?

[Amanda1]

Hi Everone,

My fingers working much quicker than my brain has obviously given rise to much confusion.

I did make a slight "typo" in my first post - what I should have said was that if the cell in Col E was not blank, the row must be deleted - instead I missed out the word "not" which gave the opposite requirement.

The reason why I want that, is because Col E is populated with data if there are matches against other worksheets, (Matt wrote me the script a couple of weeks ago). What I need to be left with, is, all the rows that have data in Col A OR Col B Or C OR so on, but blank in E, because they are uniqie items and don't "match" anything anywhere else - see, I'm not totally daft, I promise!

I'm going to try the solutions above - thanks very much for all your help - now who has got Xmas smilies.

Again, thanks for your teaching and help.

Take care

Amanda:clap: :friends:

[Amanda1]

Hi,

Two questions before I consign this to "solved"

First, Zack, please remember that some of us are plain mortals who only communicate in English, unlike the majority of the elite members of this forum who communicate in a language of their own.

I will gladly do as you told me to,


Amanda, try changing the xlCellType to constants

If I knew what you were talking about! :huh:

Remember, I'm brand new at this so talk to me as if I'm an idiot & I'll understand - as I progress from the creche stage, I'll tell you.

Then, to everyone and bear in mind what I have just said about myself being brand new - in the script that I attempted, (pieced together from bits and some of my own additions), where did I go wrong & if you've got the patience to explain that will really be the icing on the cake for me!!:cloud9:


With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 2000
For Lrow = EndRow To StartRow Step -1
If (.Cells(Lrow, "E").Value) Then .Rows(Lrow).Delete
Else
End If



It will be obvious to all of you, but not to me. It can be my lesson for today.

Thanks to everyone & I'll try not to have any more typing errors in future.

Have a great day:curtsey:

Amanda

[Zack Barresse]

.. now who has got Xmas smilies.
Ooh! Ooh! Pick me!!

:drooling3 :Drooling: :$$: :chef: :chat: :drop: :xnoel: :xmas: :rudolph: :frosty: :snowman: :santa:

[Bob Phillips]

Then, to everyone and bear in mind what I have just said about myself being brand new - in the script that I attempted, (pieced together from bits and some of my own additions), where did I go wrong & if you've got the patience to explain that will really be the icing on the cake for me!!:cloud9:


With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 2000
For Lrow = EndRow To StartRow Step -1
If (.Cells(Lrow, "E").Value) Then .Rows(Lrow).Delete
Else
End If



It will be obvious to all of you, but not to me. It can be my lesson for today.


With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 5 '2000
For Lrow = EndRow To StartRow Step -1
If (.Cells(Lrow, "E").Value) <> "" Then
.Rows(Lrow).Delete
End If
Next Lrow
End With

[Amanda1]

Ah Ha

I thought there had to be an "Else" with an "If" and a "Then" - that obviously isn't true cause you don't have it in your script.

Thanks - owe you again.

Take care

Amanda

[Bob Phillips]

I thought there had to be an "Else" with an "If" and a "Then" - that obviously isn't true cause you don't have it in your script.

Not when there is not an else condition.

[Amanda1]

OK - I'm learning - thanks

Another question. In the script line
If (.Cells(Lrow, "E").Value) <> "" Then
.Rows(Lrow).Delete


How would I change the "" to be any number as a first entry in the cell ? e.g. if the cell started with 1, 2, 3,4 ,5 6, 7, 8, or 9, (Bearing in mind the number in the cell itself could be 34526 or similar, but it will obviously start with a 3).

I've been working on this and got myself into a real mess - I managed to delete every row of the worksheet, then I managed to just end up with only one row that started with a singular 1 - the fact there were probably about 1000 rows that all began with a 1 was besides the point, I lost all those and just kept the singular "1". Its probably dead easy, but I can't get it.

Thanks for your help

Cheers

Amanda:thinking:

[Bob Phillips]

If Left(.Cells(Lrow, "E").Value, 1) = "1" Then
.Rows(Lrow).Delete

[Amanda1]

Hi

No, that isn't what I meant - What I need is <> 1 or<> 2 all the way up to 9. I've tried every permutation I can think of and none of them work for me. What is the correct way to do it

Thanks & after you tell me I will no doubt kick myself

Cheers Amanda