Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

VBA Code to avoid hard coded selection

Dear experts:

below code inserts data validation into cells E3:E1450 of the active worksheet.

I do not want to hardcode the selection but check by means of VBA Code the number of Rows in COLUMN B first and insert that number into the second part of the Range ("E3:E1450").

Example: The number of columns / filled cells of column B is 1872. This number is to be transferred to the line of code where the Range (second part of the seclection) is selected, i.e.

Range("E3:E1872").Select.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub MacroDataValidation()

    Range("E3:E1450").Select
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=DD_Range"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
0
AndreasHermle
Asked:
AndreasHermle
  • 2
1 Solution
 
kgerbChief EngineerCommented:
Hello Andreas,
Try this.

Kyle
Sub MacroDataValidation()
    With Range("E3:E" & Cells(Rows.Count, "B").End(xlUp).Row).Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=DD_Range"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Open in new window

0
 
AndreasHermleAuthor Commented:
Hi Kyle,

this did the trick! Thank you very much for your professional and swift support.  I really appreciate it.

Regards, Andreas
0
 
kgerbChief EngineerCommented:
You're welcome.  Glad to help.

Kyle
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now