vba - check if A and B appear n times in a range

[CROY1985]

Hi all

I have a vba routine which i use to import some external data and drive some reports.

As part of the process, i need to add some validation to the data that is being imported to check that each of the following appear n times (e.g. 7):

East Scotland
West Scotland
North Scotland
South Scotland
NW England
NE England
Wales
Midlands
East Anglia
SE England
South Cen England
SW England
Greater London

They would appear in column B, and my data range is A3:S107 (the 107 bit should be dynamic, the Column S would be nice to be dynamic but not of great importance)

Basically, if any of them aren't 7, i want to stop the routine. Perhaps giving a msg box with the list and their counts to highlight which area is missing/not complete.

If they are all 7, i'd like to delete all rows where column B contains something other than the list above.


Any help greatly appreciated.

 

[JLGWhiz]

I gleaned from the OP that you want to check the unique values of column B for seven occurrences, if the count does not match 7, display a message box with the option to exit the procedure.

Sub checkBfor7()
Dim sh As Worksheet, lr As Long, c As Range, ans As Variant
Set sh = ActiveSheet
lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
With ActiveSheet
    .Range("B1:B" & lr).AdvancedFilter xlFilterCopy, , .Cells(lr + 2, 1), True
    For Each c In .Cells(lr + 2, 1).CurrentRegion.Offset(1)
        If c <> "" And Application.CountIf(.Range("B1:B" & lr), c.Value) <> 7 Then
            ans = MsgBox(c & " Does not equal 7 occurences.  Do you want to continue?", vbQuestion + vbYesNo, "NOT 7")
            If ans = vbNo Then
                .Cells(lr + 2, 1).CurrentRegion.ClearContents
                Exit Sub
            End If
        End If
    Next
    .Cells(lr + 2, 1).CurrentRegion.ClearContents
End With
End Sub