VBA - With and selection 命令太慢了。我怎样才能更好地优化它以更快地运行？

Question

这个问题在这里已经有了答案:





How to avoid using Select in Excel VBA

(16 个答案)


4年前关闭。




我正在选择一个范围并在 With 中使用该选择。指挥部，众所周知，.Selection命令将减慢该过程。编写此代码以更快地运行它的更好方法是什么？

这是我的代码:

Sheets(Currentsheetname).Range("A" & SelRowNumber + 1 & ":A" & lastrow).Select

With .Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
               xlBetween, Formula1:="Remove"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "Warning"
    .InputMessage = ""
    .ErrorMessage = "Please select a value from the list available in the selected cell."
    .ShowInput = True
    .ShowError = True
End With

Answer 1

这是 Select 的样子和 Selection删除:

With Sheets(Currentsheetname).Range("A" & SelRowNumber + 1 & ":A" & lastrow).Validation

  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                            xlBetween, Formula1:="Remove"
                                            .IgnoreBlank = True
                                            .InCellDropdown = True
                                            .InputTitle = ""
                                            .ErrorTitle = "Warning"
                                            .InputMessage = ""
                                            .ErrorMessage = "Please select a value from the list available in the selected cell."
                                            .ShowInput = True
                                            .ShowError = True
End With