Autofill Cells with Drop-down List Until Last Row using VBA?

[kchuphone]

Looking for some help with autofilling using VBA

I have a table and would like to add a drop-down list (using data validation) in column B until the last row using VBA.

I tried the VBA code below but unfortunately couldn't get it to work. Any insight or pointers will be super appreciated!

	A	B
1	Sample 1
2	Sample 2
3	Sample 3

Sub listCreator()

Dim last_row As Long

last_row = Cells(Rows.Count, 2).End(xlUp).Row

Range("B1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="YES,NO"

Range("B1").AutoFill Destination:=Range("B1:B" & last_row)

End Sub

 

[rollis13]

Your data is in column A so the last cell has to be calculated on that column, change to:

last_row = Cells(Rows.Count, 1).End(xlUp).Row

 

[Peter_SSs]

Welcome to the MrExcel board!

You can also put them all in directly at once rather that just the top one and then filling down.

Sub listCreator_v2()
  Dim last_row As Long
  
  last_row = Cells(Rows.Count, 1).End(xlUp).Row
  Range("B1:B" & last_row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="YES,NO"
End Sub

BTW,
For the future, it makes it even easier for helpers if you can give your sample data with XL2BB

 

[kchuphone]

Welcome to the MrExcel board!

You can also put them all in directly at once rather that just the top one and then filling down.

Sub listCreator_v2()
  Dim last_row As Long
 
  last_row = Cells(Rows.Count, 1).End(xlUp).Row
  Range("B1:B" & last_row).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="YES,NO"
End Sub

BTW,
For the future, it makes it even easier for helpers if you can give your sample data with XL2BB

Thank you! I'm new to VBA and this is super helpful!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, you could also do it in a single code line

Sub listCreator_v3()
  Intersect(Columns("B"), ActiveSheet.UsedRange.EntireRow).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="YES,NO"
End Sub