I have a macro to check a column to see if there are any blank cells. It
starts something like this:

For Each cell in Range("F2:F1001")
If cell.Value = "" Then
MsgBox " Please check row " & row number
End if
Next cell

Its the row number I cannot get.

Thanks in advance.

Gareth

Gareth,

as a general hint, if you dimension things first, you will find it easier to
see properties and methods. I've also added an escape hatch to this so you
don't get 1000 message boxes in the event of an empty column.

Sub Test()
Dim rngCell As Range
For Each rngCell In Range("F2:F1001")
If IsEmpty(rngCell.Value) Then
MsgBox " Please check row " & rngCell.Row
Exit For
End If
Next rngCell
End Sub

--
Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in


"Gareth" wrote in message
...
I have a macro to check a column to see if there are any blank cells. It
starts something like this:

For Each cell in Range("F2:F1001")
If cell.Value = "" Then
MsgBox " Please check row " & row number
End if
Next cell

Its the row number I cannot get.

Thanks in advance.

Gareth

Hi Gareth,

Find the blank cells in the used range of the entire column F.

in Excel: Select Column F, Edit,Goto, blanks

recorded macro

Sub Macro17()
Columns("F:F").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

Macro that you apparently are looking for:, find the empty
cells in column F within the Used Range ,
Ctrl+End identifies the last cell of the used range

Sub LoopEmptyF()
Dim cell As Range
Dim xRng As Range
On Error Resume Next
Set xRng = Columns("F:F").SpecialCells(xlCellTypeBlanks)
If xRng Is Nothing Then Exit Sub
On Error GoTo 0

For Each cell In Columns("F:F").SpecialCells(xlCellTypeBlanks)
MsgBox "Row is " & cell.Row & _
", and Column is " & cell.Column & "(" & _
Left(Cells(1, cell.Column).Address(0, 0), _
Len(Cells(1, cell.Column).Address(0, 0)) - 1) & ")"
Next cell
'-- same information without a loop ---
MsgBox Columns("F:F").SpecialCells(xlCellTypeBlanks).Addr ess(0, 0)
End Sub

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gareth" wrote in message ...
I have a macro to check a column to see if there are any blank cells. It
starts something like this:

For Each cell in Range("F2:F1001")
If cell.Value = "" Then
MsgBox " Please check row " & row number
End if
Next cell

Its the row number I cannot get.

Thanks in advance.

Gareth