How to remove specific values from County table?

[vbanewbie68]

Hi

How can I get VBA to identify and remove specific county's values like London, France, USA out of the County's column?

Please see my screenshot and my VBA codes.

Also would it be possible to add a valid county table into VBA? For example if value is Scotland under County's column and it is not match with the valid county table then it should be blank.

I hope it makes sense?

Thank you in advance.

Best regards

Rej

Sub Add_Rows_Data()


'LastRow = Imprt.Cells(1, 1).End(xlDown).Row
'How to check if a column exists in the sheet, will throw an error if it doesn't exist
'Set Imprt = ThisWorkbook.Sheets("Sheet5")
LastRow = Imprt.Cells(1, 1).End(xlDown).Row


For x = 2 To LastRow



County = WorksheetFunction.Match("County", Import.Cells(1, 1).EntireRow, 0)

'Imprt.Cells(x, County).Value = England, London, Scotland France, USA 'County

 

[johnnyL]

How about:

    Dim Cell    As Range
    Dim Imprt   As Worksheet
'
    Set Imprt = ThisWorkbook.Sheets("Sheet5")
'
    For Each Cell In Imprt.Range("A2:A" & Imprt.Range("A" & Rows.Count).End(xlUp).Row)
        Select Case Cell.Value
            Case "London", "France", "USA", "Scotland"                                      ' <--- Excluded Counties. Adjust as needed
                Cell.Value = vbNullString
         End Select
    Next

 

[vbanewbie68]

How about:

    Dim Cell    As Range
    Dim Imprt   As Worksheet
'
    Set Imprt = ThisWorkbook.Sheets("Sheet5")
'
    For Each Cell In Imprt.Range("A2:A" & Imprt.Range("A" & Rows.Count).End(xlUp).Row)
        Select Case Cell.Value
            Case "London", "France", "USA", "Scotland"                                      ' <--- Excluded Counties. Adjust as needed
                Cell.Value = vbNullString
         End Select
    Next

Hiya

Thanks for this and how to convert your codes into this please? See my another screenshot to see if that is helpful for you?

Best regards

V

 

[vbanewbie68]

I am so pleased that the outcome of the county using your VBA codes is working where I have converted it into this way. Please see the VBA below. . Many thanks for your time and kind help on this matter! I am so pleased.

Best regards

V

Select Case Imprt.Cells(x, County).Value
Case "London", "LONDON", "England", "USA", "France", "Scotland", "United Kingdom" ' <--- Excluded Counties. Adjust as needed
Imprt.Cells(x, County).Value = vbNullString

End Select