Row number
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 |
Row number
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 |
Row number
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 |
All times are GMT +1. The time now is 05:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com