Remove Validation Rules Row by Row from Sheet Based on Column G Value
Hy Experts, I am using an excel sheet that has multiple columns. Every column has validation rules. I want to remove these validation rules upon selecting any row in the entire sheet. When I select the row then I will put the value in G from drop down menu. on the basis of column G value the entire row validation rules should be cleared. The sheet looks like this.
enter image description here
The code that I am using is as under.
Sub RemoveDV()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Validation.Delete
Next ws
MsgBox ("All Validation Rules has been removed successfully")
End Sub
it working fine but it delete the entire worksheet. But I want to delete the validation only row that I am working on.
Thanks
excel
asked Nov 23 at 2:27
Usman.haider Bhatti
55
add a comment |
Hy Experts, I am using an excel sheet that has multiple columns. Every column has validation rules. I want to remove these validation rules upon selecting any row in the entire sheet. When I select the row then I will put the value in G from drop down menu. on the basis of column G value the entire row validation rules should be cleared. The sheet looks like this.
enter image description here
The code that I am using is as under.
Sub RemoveDV()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Validation.Delete
Next ws
MsgBox ("All Validation Rules has been removed successfully")
End Sub
it working fine but it delete the entire worksheet. But I want to delete the validation only row that I am working on.
Thanks
excel
asked Nov 23 at 2:27
Usman.haider Bhatti
55
add a comment |
Hy Experts, I am using an excel sheet that has multiple columns. Every column has validation rules. I want to remove these validation rules upon selecting any row in the entire sheet. When I select the row then I will put the value in G from drop down menu. on the basis of column G value the entire row validation rules should be cleared. The sheet looks like this.
enter image description here
The code that I am using is as under.
Sub RemoveDV()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Validation.Delete
Next ws
MsgBox ("All Validation Rules has been removed successfully")
End Sub
it working fine but it delete the entire worksheet. But I want to delete the validation only row that I am working on.
Thanks
excel
asked Nov 23 at 2:27
Usman.haider Bhatti
55
Hy Experts, I am using an excel sheet that has multiple columns. Every column has validation rules. I want to remove these validation rules upon selecting any row in the entire sheet. When I select the row then I will put the value in G from drop down menu. on the basis of column G value the entire row validation rules should be cleared. The sheet looks like this.
enter image description here
The code that I am using is as under.
Sub RemoveDV()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Validation.Delete
Next ws
MsgBox ("All Validation Rules has been removed successfully")
End Sub
it working fine but it delete the entire worksheet. But I want to delete the validation only row that I am working on.
Thanks
excel
excel
asked Nov 23 at 2:27
Usman.haider Bhatti
55
asked Nov 23 at 2:27
Usman.haider Bhatti
55
asked Nov 23 at 2:27
Usman.haider Bhatti
55
asked Nov 23 at 2:27
Usman.haider Bhatti
55
asked Nov 23 at 2:27
Usman.haider Bhatti
55
55
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Try this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If Target.Value = "The value that will trigger the validation clearing" Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
This will trigger anytime you change a value in column "G" of the specified sheet and if the new value = the text you want.
EDIT:
Let say you store your values (values that will trigger the script) in a range on the same page you can adapt following code to your needs:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr: Arr = Application.Transpose(Range("$P$1:$P$12").Value) 'Values stored in "P1:P12"
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If UBound(Filter(Arr, Target.Value)) > -1 Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
answered Nov 23 at 2:55
Display name
55416
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53439968%2fremove-validation-rules-row-by-row-from-sheet-based-on-column-g-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If Target.Value = "The value that will trigger the validation clearing" Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
This will trigger anytime you change a value in column "G" of the specified sheet and if the new value = the text you want.
EDIT:
Let say you store your values (values that will trigger the script) in a range on the same page you can adapt following code to your needs:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr: Arr = Application.Transpose(Range("$P$1:$P$12").Value) 'Values stored in "P1:P12"
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If UBound(Filter(Arr, Target.Value)) > -1 Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
answered Nov 23 at 2:55
Display name
55416
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
add a comment |
Try this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If Target.Value = "The value that will trigger the validation clearing" Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
This will trigger anytime you change a value in column "G" of the specified sheet and if the new value = the text you want.
EDIT:
Let say you store your values (values that will trigger the script) in a range on the same page you can adapt following code to your needs:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr: Arr = Application.Transpose(Range("$P$1:$P$12").Value) 'Values stored in "P1:P12"
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If UBound(Filter(Arr, Target.Value)) > -1 Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
answered Nov 23 at 2:55
Display name
55416
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
add a comment |
Try this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If Target.Value = "The value that will trigger the validation clearing" Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
This will trigger anytime you change a value in column "G" of the specified sheet and if the new value = the text you want.
EDIT:
Let say you store your values (values that will trigger the script) in a range on the same page you can adapt following code to your needs:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr: Arr = Application.Transpose(Range("$P$1:$P$12").Value) 'Values stored in "P1:P12"
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If UBound(Filter(Arr, Target.Value)) > -1 Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
answered Nov 23 at 2:55
Display name
55416
Try this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If Target.Value = "The value that will trigger the validation clearing" Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
This will trigger anytime you change a value in column "G" of the specified sheet and if the new value = the text you want.
EDIT:
Let say you store your values (values that will trigger the script) in a range on the same page you can adapt following code to your needs:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr: Arr = Application.Transpose(Range("$P$1:$P$12").Value) 'Values stored in "P1:P12"
If Not Intersect(Target, Range("G:G")) Is Nothing And Target.Count = 1 Then
If UBound(Filter(Arr, Target.Value)) > -1 Then
Target.EntireRow.Validation.Delete
End If
End If
End Sub
answered Nov 23 at 2:55
Display name
55416
edited Nov 26 at 23:03
answered Nov 23 at 2:55
Display name
55416
answered Nov 23 at 2:55
Display name
55416
answered Nov 23 at 2:55
Display name
55416
55416
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
add a comment |
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
@ O.PAL Thanks bro its working. Now I want to use Target.Value as dynamic range as I have a drop down list in the Column G. Could it possible to use it will multiple keywords instead of single. or dynamically get work from specified list. Thanks again
– Usman.haider Bhatti
Nov 23 at 15:32
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53439968%2fremove-validation-rules-row-by-row-from-sheet-based-on-column-g-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
