|
Missing Data in a Range
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. |
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. |
Missing Data in a Range
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 -- Regards, Tom Ogilvy "Trevor Shuttleworth" wrote in message ... 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. |
Missing Data in a Range
Tom
OK, that would work too ;-) And it is just a bit shorter than my version. Regards Trevor "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Trevor Shuttleworth" wrote in message ... 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. |
Missing Data in a Range
I think that is wonderful, can you help me understand
the "mod 8 < 0" part--seems that the 8 is in reference to the "H", but what makes this work? -----Original Message----- 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 -- Regards, Tom Ogilvy "Trevor Shuttleworth" wrote in message ... 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. . |
Missing Data in a Range
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. -- Regards, Tom Ogilvy wrote in message ... I think that is wonderful, can you help me understand the "mod 8 < 0" part--seems that the 8 is in reference to the "H", but what makes this work? -----Original Message----- 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 -- Regards, Tom Ogilvy "Trevor Shuttleworth" wrote in message ... 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. . |
| All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com