Solved: Deleting blank rows in range? a better way?

[Simon Lloyd]

Hi all i have just answered a post on another forum and my soultion to their question is posted below what i would like to know is, is this the way to do it or is there a smarter way?

How do I check this column and if it is blank or has a 0 (zero) entered in it, the row is deleted?

[VBA]
Sub removeblank()
Dim Rng As Range
Dim MyCell
Dim i
i = 1
Set Rng = Range("C9:C119")
For Each MyCell In Rng
If MyCell = Null Or MyCell = 0 Then
MyCell.EntireRow.Delete
End If
i = i + 1
Next
MsgBox "There were " & i & " Rows Deleted!"
End Sub

[/VBA]Regards,
Simon

[lucas]

This will work for blanks in column C:
[VBA]Option Explicit
Sub DeleteBlankColC()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
Range("C1").Select
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text Like "[]"
If test = True Then Cells(x, col).EntireRow.Delete
Next
End Sub[/VBA]

[Bob Phillips]

It certainly is NOT the way to do it.

You should never delete in a top-down loop, you miss rows if you have two blanks in a row. You should delete bottom-up, find the last row of data and work backwards

[vba]

Dim i As Long

For i = 119 To 9 Step -1
If Cells(i, "C").Value = "" Then
Rows(i).Delete
End If
Next i
[/vba]
If you must go top down, build up a range as you go and delete at the end

[vba]

Dim cell As Range
Dim rng As Range

For Each cell In Range("C9:C119")
If cell.Value = "" Then
If rng Is Nothing Then
Set rng = Rows(cell.Row)
Else
Set rng = Union(rng, Rows(cell.Row))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
[/vba]
A more efficient way is to use autofilter, filter by blanks, and delete visible rows

[vba]

With Range("C9:C119")
.AutoFilter Field:=1, Criteria1:="=""""", Operator:=xlAnd
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
[/vba]
Another way with blanks is to get the blanks cells using SpecialCells

[vba]

Range("C9:C119").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
[/vba]

[mdmackillop]

Hi Simon,
I'll go with XLD here, and the best way to include 0 is to find and clear them first.
[vba]
Sub removeblanks()
Dim Rng As Range
Dim MyCell
Dim i
Set Rng = Range("C9:C119")
With Rng
Set C = .Find(0, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

[/vba]

[Simon Lloyd]

Wow! didnt expect such a response thanks guys.....Bob! very passionate! and your right at first i did get a miss when finding two blanks together, well as you can see it just shows that my learning curve has still to reach the curve!

Could those of you who agree post back an "Ok" for me to post your solutions (in their entirity, name as well) to the other forum?

Regards,
Simon

[mdmackillop]

OK with me.

A word of caution,
[VBA]
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[/VBA]
has a limit. I don't know how many cells it will handle, but on test it won't to 20,000 or so.

[malik641]

A more efficient way is to use autofilter, filter by blanks, and delete visible rows

[vba]

With Range("C9:C119")
.AutoFilter Field:=1, Criteria1:="=""""", Operator:=xlAnd
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
[/vba]

That's a nice trick. I'll remember this one. Thanks Bob

[Zack Barresse]

The limit is around 8132 or so. It's a limitation on SpecialCells and being able to recognize that number of unique formats.

And I use the SpecialCells all the time. If you come up to the limit, you have too many formats!

Edit: As a side note, Bob's autofilter code should have an additional line at the start..

[vba] Sheets("Sheet1").AutoFilterMode = False[/vba]

.. or whatever the sheet name is. Again, for error proofing.

[Bob Phillips]

Fine by me.

[drums4monty]

Hi All

I am the person on the other forum that Simon was trying to help. I have tried all of your replies but none work, I get a box pop up with 400 in it. I could send a sample if someone wants it.

Many thanks

Alan

[mdmackillop]

Hi Alan
Welcome to VBAX
Please submit your sample. Use Manage Attachments in the Go Advanced section

[drums4monty]

Hi MD, I hope that I have done this right. The Sample is a cut down version but the rend of the shhet continues down to C119

Alan

[drums4monty]

Sorry, that should say . . . but the trend of the sheet . . . .

[mdmackillop]

Which Sheet/column are you running this on?

[drums4monty]

Its sheet 1, and column c gets filled in, i want to delete the colums that have either nothing or 0 (zero) in so I am left with the colums with 1 or above

[mdmackillop]

My code from Post 4 is running OK with me. It may be a problem with merged cells. Try unmerging all cells in column C

[drums4monty]

Thats done it MD, it was the merged cells. Now onto my other problem. I get sent these worksheets by email by several seperate offices. Is there a way to run the macro on my PC with out it being part of their worksheet? Also there is another sheet in the workbook that i need to run the code on (i think i can adapt your code to work on the other sheet as it similar), so I would need to run the code to do its job on sheet 1, then go to sheet 2 and run he code on that one. Is this possible?

[mdmackillop]

Definitely possible.
Save the macro in Personal.xls and it will run on the active woirkbook.

To run it on two sheets you need to loop
[VBA]
Sub RemoveBlanksTwo()
Dim Rng As Range
Dim MyCell
Dim i as Long
For i = 1 To 2
Set Rng = Sheets(i).Range("C9:C119")
With Rng
Set c = .Find(0, LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
Next
End Sub

[/VBA]

[drums4monty]

Thanks Md, thats brilliant, many thanks for your help.

Regards

Alan

[drums4monty]

Hi again MD

I have written a (crude) bit of code that adds a 1 into a cell so that when I run your delete row code I still get the row with the item description on, this works fine, how do I make your code run straight after my code, i.e. I only have to run one macro, so I run mine and when it has done it runs yours without me having to tell it to run yours manually?