VBA select cells for data validation

[Ambre]

Hi,

What I am trying to do is a macro to validate some data into List.

I have several colonnes (19) and, for example, from cell B15 to last row I want to validate the data with the list List_HMG.
I wrote the code for each column but it is not running well because at the end of the macro, all selected cells during the marco have the same drop down list. I understand that the second selection is added to the first one, the third one to the second+first etc. I guess I need the cells to be selected indenpendtly and not keep in the selection the first cells selected (hope it makes sense).
Here is an extract from my code, if you have any ideas...

Range("B15", Selection.End(xlDown)).Select
     With Selection.Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List_HMG"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
 
    Range("C15", Selection.End(xlDown)).Select
     With Selection.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List_Buyers"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

and if - Selection.End(xlDown)) -
is not right to go to the last row, please advise me

Other question:
There are several ranges to which I want to assign the same list.
How can I wrote the code?

Something like that?

Range("B15", Selection.End(xlDown)) And Range("E15", Selection.End(xlDown)).Select 'i know this is not correct 
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List_Yes_No"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

To tell you the whole story, I first run a macro to add rows to this sheet but the new cells do not get the dropdown lists so I run this second macro to apply validation to the new cells and old ones also again.
I will add many rows in the existence of this file and I want the data validation to apply automatically.

Thank you
Ambre


I am a beginner in VBA
English is not my mother tongue

 

[Ambre]

Solution to my first problem
the code should be

Range("B15").Select
    Range(Selection, Selection.End(xlDown)).Select

instead of

Range("B15", Selection.End(xlDown)).Select