Sleeper: Delete rows in mailmerge data source

[Zack Barresse]

The following quote comes from this thread (http://www.vbaexpress.com/forum/showthread.php?t=2373). Split by kpuls since it is a different issue.


.. PS : Is there a simple way to chk if a complete row is empty and then delete it ?

Sure. Maybe something like this ...



Sub DeleteIfNotComplete()
If WorksheetFunction.CountA(Range("A1:D1")) <> 4 Then Range("A1").EntireRow.Delete
End Sub

[Ken Puls]

PS : Is there a simple way to chk if a complete row is empty and then delete it ?

Hmmm... Zack, I interpretted that differently... I would have said:


Sub DeleteIfNotComplete()
If WorksheetFunction.CountA(Range("A1:IV1")) = 0 Then Range("A1").EntireRow.Delete
End Sub

Tinku, the difference is that in mine, if all cells are empty in the row, it will delete it. In Zack's example, if there is data in 1-3 cells in his range, but not all 4, then it will delete the entire row.

Not 100% sure which you want, but figured I'd post the option.

[Tinku]

Hi firefytr & kpuls

Thank you for your quick feedbacks. But I think kpuls code is the one I am looking for as I want to delete those rows which are coompletely blank.
Also is there a way to loop thru the entire file so that I dont have to individually delete each row.

Regards
Tinku

[Ken Puls]

Hi Tinku,

Sure. You're just trying to remove the blank rows in the middle of your data, correct? Try this (just save your workbook first in case it doesn't do what you want.):


Sub DeleteIfNotComplete()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Range("A" & x & ":IV" & x)) = 0 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub

This is based on DRJ's KB Entry here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=135), modified to use the line of code above.

[Tinku]

Thanks kpuls

Worked like a charm.. another quick question.. can I use the above code to delete rows based on whether some cells in that row are blank ?

Regards
Tinku

[Ken Puls]

Sure you can. We'd need to mod it a bit though.

What would the condition be? Ie, if Column B is blank, or something else...

Just so you know, we're okay with you starting new threads as well. We try to keep related stuff together sometimes, but we also don't to lose questions buried too deeply either. Totally up to you.

Let me know on the above though. :yes

[Tinku]

Thanks kpuls

I will continue on this thread.. longest thread I have had.. hehe.. I want the above code to be reusable based on 5 or 6 columns (not any particular order) being blank..

Regards

Tinku

[Ken Puls]

Okay, I think you lost me.

Are you saying that if more than 5 columns are blank in a range that you'd want the row deleted? If so, how many columns of data are on the sheet that you use...

If I'm off base, can you lay out a case of what would trigger the macro, and what wouldn't? Even a quick sample workbook upload would be a help.

[Tinku]

Hi kpuls

I have attached a sample file and marked the rows with yellow background which I want deleted. As you will see from the sample there is no fixed criteria or set pattern for deleting rows but based more on how much data that row is holding. So I just want it to be flexible, so that I can decide which row stay and which get deleted based on data in the columns.

Regards
Tinku

[Ken Puls]

Hi Tinku,

I had a look through your data, and I'm not sure how I would tackle this. The problem is, as you say, that there isn't really a fixed pattern. This gives rise to the issue that I'm not really sure how to tell Excel what to delete vs what not to delete. And the last thing we want to do is delete something you need.

What I would probably do in this case is tackle it as follows:

-Write a routine to highlight the rows that I think I want deleted
-I'd review those manually, highlighting others and unhighliting ones I did want
-Then I'd run a routine to delete all highlighted rows

Until you can build a consistent logic of how to make the computer choose for you, I don't really know another way.

I'm going to split this thread, as well, as this seems to be progressing into another issue, and somone else may be able to help. (We don't want to have them avoid the trhead since it's become so long already :))