Determining whether there is a blank Row 1

[Suggie]

I'm pretty new to VBA

I have a problem where I need excel to tell me whether there is a blank row or not. The code I am using is:

ActiveCell.FormulaR1C1 = _
&quot;=IF(OR(RC[4]<>0,RC[5]<>0,RC[6]<>0,RC[7]<>0,RC[8]<>0,RC[9]<>0,RC[10]<>0,RC[11]<>0,RC[12]<>0,RC[13]<>0),R2C5,&quot;&quot;Blank Row&quot;&quot&quot;

If one of the cells has data in it I want to keep the row, if not, the next code below deletes the rows that have been flaged as 'blank row' using the above formula

Dim Rng1 As Range
Dim Rng2 As Range

Set Rng1 = Intersect(Selection.Cells(1).EntireColumn, Selection.Parent.UsedRange)
Rng1.Replace &quot;Blank Row&quot;, &quot;&quot;, xlWhole
On Error Resume Next
Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
If Rng2 Is Nothing Then
'nothing to delete
Else
Rng2.EntireRow.Delete
End If

The problem is that the first formula is not flaging the rows that are completely blank. I think this may be a result of the cells containing data validation formulas, but the content are blank.

Has anyone any suggestions.

TIA,
Mark

 

[xlbo]

You miss copied the formula - this works:

=IF(AND(ISBLANK(C1:F1)), &quot;Blank Row&quot;,&quot;Data Contained&quot

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Suggie]

Thanks again for everyone's help, it is much appreciated and I just look forward to the day that I can repay the favour, (have a feeling it may be some time away!)

 

[ashishbs]

Hi Suggie,

If u wish to do this in Excel Macro

'---------------------
Public Sub CheckForBlankRows()
Dim X As Integer
Dim Rng As Range

Set Rng = ActiveSheet.UsedRange.Rows

For X = 1 To Rng.Rows.Count
If Application.WorksheetFunction.CountA(Rng.Rows(X).EntireRow) = 0 Then 'Check if Entire Row is Blank
MsgBox Rng.Rows(X).Address 'Returns the Cell address , which is Blank.
End If
Next X
End Sub