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

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