Missing Data in a Range
Jeff
one way:
Sub Test()
Dim MaxRow As Long
Dim ExpectedCells As Long
MaxRow = WorksheetFunction.Max( _
Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row, _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row, _
Range("G65536").End(xlUp).Row, _
Range("H65536").End(xlUp).Row)
ExpectedCells = WorksheetFunction.CountA(Range("A1:H" & MaxRow))
If ExpectedCells < MaxRow * 8 Then
MsgBox "Incomplete data"
End If
End Sub
Regards
Trevor
"Jeff Green" wrote in message
...
Is there a simple code to search a known range to verify
that each cell has correct data? The catch here is that
I always use columns A thru H, but do not know how many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header), then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...
I currently use a Do...Loop Until IsEmpty and then again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.
Thanks if you could help, or let me know if I should
provide more detail.
|