Missing Data in a Range

[Jeff Green]

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.

 

[Trevor Shuttleworth]

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

 

[Tom Ogilvy]

That is a good suggestion. It made me think

how about
if application.countA(Range("A:H")) mod 8 <> 0 then
msgbox "Missing Data"
End if

 

[Trevor Shuttleworth]

Tom

OK, that would work too ;-) And it is just a bit shorter than my version.

Regards

Trevor

 

[Guest]

I think that is wonderful, can you help me understand

 

[Tom Ogilvy]

16 mod 8 would return 0. Mode gives you the remainder of dividing 16 by 8
(in the example). since each row should have 8 entries, and countA counts
the cells with entries, one would expect that a square - filled range of 8
columns should have a countA evenly divisible by 8.