Row number

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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
 
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).Address(0, 0)
End Sub
 
Back
Top