Creating a macro to generate a list for hundreds of rows

Hi -

I'm trying to create a macro that will create a list for hundreds of rows. I created the first portion of the macro, however needing help on how to apply from rows 6-462. Please note each row has specific selection options.

 Range("D5").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$AU$5:$AX$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

As you can see cell D5, needs to have the list options of AU5:AX5. Looking on how to carry this down to D462 with the list option of AU462:AX462. Any help is greatly appreciated.

Hi kcgojnur,

Try the following Macro. Changes in red. In most cases 'Select' is not needed, and causes Macros to run slowly.

Sub CreateValidationList()

  Dim r As Range
  Dim iRow As Long
  Dim sFormula As String
  
  For Each r In Range("D5:D462")
    
    'Get the Row Number
    iRow = r.Row
    
    'Create the Formula (2 leading and trailing quotes allow quotes to be in the final formula)
    'Formula is of the Form: "=$AU$5:$AX$5"
    sFormula = "" & "=$AU$" & iRow & ":$AX$" & iRow & ""

    'Apply Data Validation
    With r.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
           xlBetween, Formula1:=sFormula
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
      End With
    
   Next r
   
End Sub

Lewis