getting macro dynamic

**flunzy** · 09-04-2013, 07:38 AM

Hi guys,

I'm struggling with a code which a found on the forum. The code is perfect, only it isn't dynamic. I've tried almost everything that I know but I don't get it dynamic.

The goal is to fill every cell from M3 until the end of row in column A. So if A ends at A43 than the code should work until M43.

    With Range("M3:M").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
      .IgnoreBlank = True
     .InCellDropdown = True
     .InputTitle = ""
     .ErrorTitle = ""
     .InputMessage = ""
     .ErrorMessage = ""
     .ShowInput = True
     .ShowError = True
End With

I hope that you can help me guys.

Kind regards,

Soufian

**millz** · 09-04-2013, 07:55 AM

Try this:

last = Range("A3").End(xlDown).row
With Range("M3:M" & last).Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"
      .IgnoreBlank = True
     .InCellDropdown = True
     .InputTitle = ""
     .ErrorTitle = ""
     .InputMessage = ""
     .ErrorMessage = ""
     .ShowInput = True
     .ShowError = True
End With