macro to delete entire rows if certain cells are empty

How about this:

Please Login or Register  to view this content.

apologies for that royUK.

i'm just running the code blane245 and it seems to be taking forever it wouldn't be looping would it ?

wrightie.

I received a type mismatch error when the macro eventually finished. The macro didn't remove any rows either.

It's taking along time because it's looping through the entrie collection of 65000+ rows of the sheet. You could use the UsedRange property to restrict the loop:

Please Login or Register  to view this content.

Where is it getting a type mismatch error and are there rows with B, C, and D all empty? BTW, blanks are not empty.

Could you send me an example and I will debug it.

Oops, that example is password protected.

Doh, sorry. Hope this one is ok for you.

Try Step6(). I deletes the rows from the bottom to the top.

Please Login or Register  to view this content.

All your help is much appreciated and this works great if it's only a couple of hundred rows.

But with the amount of rows that I'm using it's just too slow.

Is there anyway I can speed it up ?

Cheers
Wrightie

There is another way to do this that I can think of, but it involves using another worksheet. Basically, you select all of the rows that you want to keep, copy them to another worksheet. Delete the original and then remain the new sheet to the old name. Does that cause you a problem? If not, I'll send you that code.

No that wouldn't be a problem. I'm intrigued ....

Hi,

The other way is to give the range till the max rows rather than checking from last row.


Regards,
Shekar.

I'm not sure how I would do that Shekar

Hi,

Please Login or Register  to view this content.

Regards,
Shekar.

Here's a new step 6 that should work. There are a couple of tricks to watch out for.
1. Don't put this macro in in the sheet that is being deleted. It will be deleted as well. As you will see, I have hard coded the name of the worksheet to be processed as "Sheet1". You may want to do that a different way.
2. The UsedRange property does not start at row 1, but at the first nonblank row. This will cause all rows before the first non-blank row to be removed.

Enjoy

' delete specific rows from a worksheet by copying the good ones to another sheet and deleting the old one
Sub Step6()

Dim myRange As String ' String to hold the range variables of the good cells
myRange = ""
Dim mySheet As String ' the name of the worksheet to be revised

Dim oldSheet As Worksheet ' the sheet we are woring on
Set oldSheet = ActiveWorkbook.Worksheets("Sheet1")
mySheet = oldSheet.Name

' set up to loop through all of the rows in the used range on the worksheet
Dim n As Long
Dim nlast As Long
Dim rw As Range
Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
nlast = rw.Count
For n = 1 To nlast
' build the list of rows to be detained
If (rw.Cells(n, 2).Value = "" And rw.Cells(n, 3).Value = "" And rw.Cells(n, 4).Value = "") Then
' this row is to be deleted, so do nothing
Else
' add this row the the list of rows to be retained
' rw.row is the first row of the unsed range of the worksheet
myRange = myRange & CStr(n + rw.Row - 1) & ":" & CStr(n + rw.Row - 1) & ","
End If
Next n
rw.Select
' myRange now has a list of rows to be retain. It may be null.
If (myRange = "") Then ' do nothing
Else
' remove the trailing "," from the range
myRange = Mid(myRange, 1, Len(myRange) - 1)

' copy the good rows range to a new workseet and get rid of the old one

' create a new worksheet and put it after the old sheet
Dim newSheet As Worksheet
Set newSheet = ActiveWorkbook.Worksheets.Add(after:=oldSheet)

' copy the good rows to the new sheet
oldSheet.Activate
On Error Resume Next
ActiveSheet.Range(myRange).Select
If (Err.Number <> 0) Then
MsgBox Err.Description
Stop
End If
Selection.Copy
newSheet.Paste

' delete the old sheet and rename the new one
Application.DisplayAlerts = False ' don't show the delete confirm popup
oldSheet.Delete
newSheet.Name = mySheet
Application.DisplayAlerts = True

End If

End Sub

blane245 , after a little bit of tweaking it works perfectly.

Many thanks for all your help blane245 and your input shekar goud it was much appreciated.