使用VBA代码将Excel中的数据验证(下拉列表)放入整个列中

Question

>我已经设法将数据验证(下拉列表(插入到列中的一行中。但是，我想将数据验证扩展到最后一行。

在最后一行之前，我找不到有关扩展数据验证的任何地方。 澄清一下，这不是要更改数据验证列表，而是要在每一行中进行数据验证，直到最后一行。

Sub datavalidation()
Dim ws As Worksheet
Dim tbl As ListObject
Dim neC As Range

Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table1")
Set neC = tbl.DataBodyRange(1, 3)
With neC.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=Table2"
.ErrorMessage = "Month"
.ErrorMessage = "Please select January until December from the list"
End With
End Sub

代码运行流畅。 但是，如果我希望将数据验证应用于下一行直到最后一行，我应该在哪里添加/更改？

Answer 1

您可以将验证列表应用于整个范围。只需扩展范围即可满足您的需求。

以下代码非常基本，用作示例。对于示例，我选择了 col A 到 C 来填充验证列表，从第 1 行到 C 列中最后一个填充的行。

Sub FillRangeWithValidationLists()
Dim ws As Worksheet, rng As Range
Dim lrow As Long
Set ws = ActiveSheet 'not recommended to use the active sheet
Set ws = ThisWorkbook.Sheet1 'this would be a better method
lrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row 'last row in column C 
Set rng = ws.Range("A1:C" & lrow) 'populate the rng variable with range you want
With rng.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=Table2"
.ErrorMessage = "Please select January until December from the list"
End With
End Sub

Answer 2

只需更改(ofc 更改工作表名称的工作表1(

lastRow = sheets("Sheet1").cells(rows.count,1).end(xlup).row
Formula1:="=Sheet1!A1:A"&lastRow