delete rows if no data exist 1

[Shift838]

I want to be able to delete entire rows if any cell within the selected area is blank.

I know VBA can do it. I have been able to remove rows if all cells are blank, but cannot get it to work if one cell within the row is blank. I have over 95000 rows to scan with 3 columns.

The code I used to remove rows if all cells within the row was blank is:

Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.



'We use Long in case they have over 32,767 rows selected.

Dim i As Long 



	'We turn off calculation and screenupdating to speed up the macro.

	With Application

		.Calculation = xlCalculationManual

		.ScreenUpdating = False

      

	'We work backwards because we are deleting rows.

	For i = Selection.Rows.Count To 1 Step -1

		If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

			Selection.Rows(i).EntireRow.Delete

		End If

	Next i



		.Calculation = xlCalculationAutomatic

		.ScreenUpdating = True

 	End With

End Sub

I found the below code that claims to do what I need, but it does not work.

Sub DeleteBlankRows2()

'Deletes the entire row within the selection if _

 some of the cells WITHIN THE SELECTION contain no data.

On Error Resume Next

Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

On Error GoTo 0

End Sub

 

[SkipVought]

Hi,

Are you saying that you want to delete the row if ANY cell in the row for the three columns, is empty?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this (typed, untested) ?

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    For i = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(i)) [!]< 3[/!] Then
            Selection.Rows(i).EntireRow.Delete
        End If
    Next i
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Shift838]

yes, I want to delete the row if any of the cells within that row have no data.

 

[SkipVought]

Simplest METHOD: Use the AutoFilter to display (blanks). Select the ROWS and right-click DELETE.

Use your macro recorder if you want to code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I want to delete the row if any of the cells within that row have no data
So, did you try my suggestion ?

 

[AnotherHiggins]

One line should do it for you:

    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Shift838]

Thanks for the help guys with your code it works.

 

[chandlm]

Nicely done AnotherHiggins!


Hope this helps.

Matt

 

[AnotherHiggins]

Thanks!


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.