Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • 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
Andreas Hermle
Asked:
Andreas Hermle
  • 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
 
Andreas HermleTeam leaderAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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