1

I am selecting a range and using that selection inside the With Command and as you all know, the .Selection command is going to slow down the process. What is the better way to write this code, to run it faster?

Here goes my code:

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
Vityata
  • 42,633
  • 8
  • 55
  • 100

2 Answers2

2

Here is what it looks like with Select and Selection removed:

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
braX
  • 11,506
  • 5
  • 20
  • 33
2

This is how to remove the selection and to speed up the process:

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

Always try to avoid seletction and activation in VBA.

Vityata
  • 42,633
  • 8
  • 55
  • 100