PDA

View Full Version : Solved: Deleting blank rows in range? a better way?



Simon Lloyd
11-17-2006, 10:23 AM
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?


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

Regards,
Simon

lucas
11-17-2006, 10:42 AM
This will work for blanks in column C:
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

Bob Phillips
11-17-2006, 10:48 AM
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



Dim i As Long

For i = 119 To 9 Step -1
If Cells(i, "C").Value = "" Then
Rows(i).Delete
End If
Next i

If you must go top down, build up a range as you go and delete at the end



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

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



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

Another way with blanks is to get the blanks cells using SpecialCells



Range("C9:C119").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

mdmackillop
11-17-2006, 10:51 AM
Hi Simon,
I'll go with XLD here, and the best way to include 0 is to find and clear them first.

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

Simon Lloyd
11-17-2006, 11:02 AM
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
11-17-2006, 11:11 AM
OK with me.

A word of caution,

Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete


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
11-17-2006, 11:12 AM
A more efficient way is to use autofilter, filter by blanks, and delete visible rows



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

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

Zack Barresse
11-17-2006, 11:30 AM
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..

Sheets("Sheet1").AutoFilterMode = False

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

Bob Phillips
11-17-2006, 01:14 PM
Fine by me.

drums4monty
11-17-2006, 05:09 PM
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
11-17-2006, 05:12 PM
Hi Alan
Welcome to VBAX
Please submit your sample. Use Manage Attachments in the Go Advanced section

drums4monty
11-17-2006, 05:16 PM
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
11-17-2006, 05:20 PM
Sorry, that should say . . . but the trend of the sheet . . . .

mdmackillop
11-17-2006, 05:22 PM
Which Sheet/column are you running this on?

drums4monty
11-17-2006, 05:26 PM
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
11-17-2006, 05:33 PM
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
11-17-2006, 05:47 PM
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
11-17-2006, 06:01 PM
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

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

drums4monty
11-18-2006, 01:43 AM
Thanks Md, thats brilliant, many thanks for your help.

Regards

Alan

drums4monty
11-18-2006, 02:13 AM
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?

Bob Phillips
11-18-2006, 02:40 AM
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



Sub YourMacro()

'your code

Call MalcolmsMacro

End Sub

drums4monty
11-18-2006, 03:05 AM
Mzny thanks XLD

Alan

mdmackillop
11-18-2006, 03:28 AM
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

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

drums4monty
11-18-2006, 03:52 AM
Thanks Malcolm, although I must admit Im not sure what it all means and what Im supposed to do with it.

mdmackillop
11-18-2006, 04:14 AM
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
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

drums4monty
11-18-2006, 04:20 AM
Actually Malcolm I do need to do the same thing on Sheet 3 with the range C7:C93

mdmackillop
11-18-2006, 04:23 AM
So now you have the means! :yes

drums4monty
11-18-2006, 07:04 AM
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.

mdmackillop
11-18-2006, 07:09 AM
Make this change

Set C = .Find(0, LookIn:=xlValues, LookAt:=xlWhole)

drums4monty
11-18-2006, 07:18 AM
Thanks Malcolm, that works a treat.

Alan

Zack Barresse
11-18-2006, 03:58 PM
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.

Simon Lloyd
11-18-2006, 06:07 PM
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

Bob Phillips
11-19-2006, 05:54 AM
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.

malik641
11-19-2006, 09:34 AM
From that he should realise that VBAXExpress is much better than ExcelForum.We rock :thumb

Zack Barresse
11-19-2006, 02:59 PM
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. :)

drums4monty
11-19-2006, 04:11 PM
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

mdmackillop
11-19-2006, 04:47 PM
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