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

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?

Quote:

---

Originally Posted by Another Forum

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

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]

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]

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]

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

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.

Quote:

---

Originally Posted by xld

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 :thumb

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.

Fine by me.

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

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

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

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

Which Sheet/column are you running this on?

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

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

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?

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]

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

Regards

Alan

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?

Quote:

---

Originally Posted by 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?

---

Call Malcolm's immediately after your code, like so

[vba]

Sub YourMacro()

'your code

Call MalcolmsMacro

End Sub
[/vba]

Mzny thanks XLD

Alan

You should also be aware that you can pass information to a sub routine, allowing you to use it more flexibly. If you store this version of the Delete sub in Personal.xls, you can pass the sheet number (or name with a small change) and range on which the code should be run. This will remove blanks from Sheet 1, Range C9:C190
[vba]
Sub RemoveBlanks()
Run "PERSONAL.XLS!RemoveBlanksSelect", 1, "C9:C190"
End Sub

Sub RemoveBlanksSelect(i As Long, Addy As String)
Dim Rng As Range
Dim MyCell
Set Rng = Sheets(i).Range(Addy)
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]

Thanks Malcolm, although I must admit Im not sure what it all means and what Im supposed to do with it.

While you may not require it in this instance, the range of cells you wish to check for and remove blanks may not be identical in each sheet. By passing this information to the macro, you can use the same basic code for multiple situations. eg
[VBA]Sub YourMacro()

'your code

Run "PERSONAL.XLS!RemoveBlanksSelect", 1, "C9:C190" 'Sheet 1 Column C
Run "PERSONAL.XLS!RemoveBlanksSelect", 2, "D9:D190" 'Sheet 2 Column D

End Sub [/VBA]

Actually Malcolm I do need to do the same thing on Sheet 3 with the range C7:C93

So now you have the means! :yes

I have just found an error. When column C has a figure with a 0 (zero) in e.g. 10, 20 etc. that row also gets deleted when it should be kept.

Make this change
[vba]
Set C = .Find(0, LookIn:=xlValues, LookAt:=xlWhole)
[/vba]

Thanks Malcolm, that works a treat.

Alan

I'll go ahead and mark this thread as Solved then! :yes

FYI, in the future, the OP (Original Poster) can mark their thread as Solved by going to Thread Tools | Mark Thread Solved | Perform Action.

FireFytr, i would have marked it solved as i have done my other threads but it seems that it had a little way to run as the new poster DrumsFor Monty (Alan) had some unsolved issues with it, he joined this thread (and forum) because i posted the thread in Excel Forum after answering his question but with the lessons from the rest of you i hadnt done him justice!

Regards,
Simon
:thumb

Quote:

---

Originally Posted by Simon Lloyd

FireFytr, i would have marked it solved as i have done my other threads but it seems that it had a little way to run as the new poster DrumsFor Monty (Alan) had some unsolved issues with it, he joined this thread (and forum) because i posted the thread in Excel Forum after answering his question but with the lessons from the rest of you i hadnt done him justice!

---

From that he should realise that VBAXExpress is much better than ExcelForum.

Quote:

---

Originally Posted by xld

From that he should realise that VBAXExpress is much better than ExcelForum.

---

We rock :thumb

LOL! Thanks Bob, I'll take that as a compliment. :)

Personally, I agree.

Simon, glad we could all work it out and contribute in some fashion. :)

Hi all

If mostly the help of Malcolm I have a working code that does what I need. Thank you all for your help and to Simon for pointing me in this direction. The help I received in this forum is excellent.

Alan

Hi Alan,
Most answers here are a combined effort and make use of each others efforts. Bob provided the Blanks line, I tagged on a standard Find routine.
Glad to help out.
Regards
MD