Excel为更新的列表宏选择范围内的最后一个值 [英] Excel selecting last value in range for a updated list macro

问题描述

在以下方面，我将不胜感激....

I'd appreciate help with the following....

我正在使用验证列表功能，但我需要人们轻松地将其添加到列表中.因此，我记录了一个宏，并将通过按钮将其链接，因此他们只需要添加一个新值并单击按钮.问题是我需要它在该列中找到最后一个填充的单元格.我在网上和这里都看过，但是由于我是VBA的新手，所以我不知道如何编辑它.

I'm using a validation list function, but I will need people to add to the list easily. So I recorded a macro and will link it from a button, so they just need to add a new value and click the button. The problem is I need it to find the last populated cell in that column. I've looked online and on here, but because I'm completely new to VBA I can't figure out how to edit it.

这是代码，我需要它选择D4作为D列中最后一个填充的值.

Here is the code, I need it to select D4 to the last populated value in column D.

Range("F5").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Filters!$D$4:$D$21"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
Range("G15").Select

谢谢！

推荐答案

有两种简单的方法可以解决这个问题.

There are 2 easy ways to deal with this.

1. 格式化表格
2. 使用 Offset()
的命名范围

1. Formatted Tables
2. Named Range using an Offset()

只需将您的列表放在另一个 admin/config/list 工作表上，即可将其删除.

Just put you list(s) on another admin/config/list sheet to get it out of the way.

如果您搜索 Excel动态验证列表

http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/

http://www.contextures.com/xlDataVal02.html

https://www.extendoffice.com/documents/excel/4021-excel-data-validation-dynamic-range.html

https://chandoo.org/wp/2010/09/13/dynamic-data-validation-excel/

http://www.techrepublic.com/article/pro-tip-create-a-dynamic-validation-control-in-excel/

这篇关于Excel为更新的列表宏选择范围内的最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！