Problem with validation deletion/modification
 

Hi, sheet1 col A is filled with values, I want col B to have a validation
based on a named range on sheet2. The problem, # the number of rows that
have validaton in sheet1!col b should change based on how many rows have
values in col a, also, the actual list will change due to changes to the
named range. I thought of deleting the validation and recreating every time
the macro runs. Also, the code below added it, then I wrote some code to
delete it and now code that added it befare no longer does, why?

On Error Resume Next
ThisWorkbook.Names("temp").Delete
test = "$A$5:$A$" & intUniqueTrDesc + 4
Sheets(RT).Range(test).Name = "temp"

With Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Temp"

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With
On Error GoTo 0

Problem with validation deletion/modification
 

Hi Mike

You don't actually need vba so solve this problem. Your validation
list needs to be based on a dynamic range. In sheet 2 say your
validation list is in Column A, A2:A10

Define a named range called test, Insert - Name - Define - and it
would look like this

=OFFSET(Sheet2!$A$2:$A$300,0,0,COUNTA(Sheet2!$A$2: $A$300),1)

This is a moving range. Now set your validation on sheet 1 to point to
=Test

This will work if you add or remove names from your validation list.
It assumes your validation list won't be longer than 300 rows.

Regards

Marcus

mikeb wrote:
Hi, sheet1 col A is filled with values, I want col B to have a validation
based on a named range on sheet2. The problem, # the number of rows that
have validaton in sheet1!col b should change based on how many rows have
values in col a, also, the actual list will change due to changes to the
named range. I thought of deleting the validation and recreating every time
the macro runs. Also, the code below added it, then I wrote some code to
delete it and now code that added it befare no longer does, why?

On Error Resume Next
ThisWorkbook.Names("temp").Delete
test = "$A$5:$A$" & intUniqueTrDesc + 4
Sheets(RT).Range(test).Name = "temp"

With Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Temp"

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With
On Error GoTo 0

Problem with validation deletion/modification
 

Marcus, thanks, what about the first probelm, where I want the validation on
sheet1 column B that you showed to appear depending on the # of rows in col
a, after the last cell in col a there should be no validation in col b? any
ideas?

"marcus" wrote:

Hi Mike

You don't actually need vba so solve this problem. Your validation
list needs to be based on a dynamic range. In sheet 2 say your
validation list is in Column A, A2:A10

Define a named range called test, Insert - Name - Define - and it
would look like this

=OFFSET(Sheet2!$A$2:$A$300,0,0,COUNTA(Sheet2!$A$2: $A$300),1)

This is a moving range. Now set your validation on sheet 1 to point to
=Test

This will work if you add or remove names from your validation list.
It assumes your validation list won't be longer than 300 rows.

Regards

Marcus

mikeb wrote:
Hi, sheet1 col A is filled with values, I want col B to have a validation
based on a named range on sheet2. The problem, # the number of rows that
have validaton in sheet1!col b should change based on how many rows have
values in col a, also, the actual list will change due to changes to the
named range. I thought of deleting the validation and recreating every time
the macro runs. Also, the code below added it, then I wrote some code to
delete it and now code that added it befare no longer does, why?

On Error Resume Next
ThisWorkbook.Names("temp").Delete
test = "$A$5:$A$" & intUniqueTrDesc + 4
Sheets(RT).Range(test).Name = "temp"

With Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Temp"

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With
On Error GoTo 0

Problem with validation deletion/modification
 

Hi Mike

You could try this which would find the last row in Col A and delete
the next 100 rows. This would clean the validation out of col b
assuming your list was no longer than 100 rows long. You can alter
this to how ever many rows suit by changing the number in brackets,
(100).

Sub removerows()

Dim t As Range

Set t = Range("A65536").End(xlUp).Cells(2, 1)
t.Resize(100).EntireRow.Delete

End Sub


Regards

Marcus

Hope this
mikeb wrote:
Marcus, thanks, what about the first probelm, where I want the validation on
sheet1 column B that you showed to appear depending on the # of rows in col
a, after the last cell in col a there should be no validation in col b? any
ideas?

"marcus" wrote:

Hi Mike

You don't actually need vba so solve this problem. Your validation
list needs to be based on a dynamic range. In sheet 2 say your
validation list is in Column A, A2:A10

Define a named range called test, Insert - Name - Define - and it
would look like this

=OFFSET(Sheet2!$A$2:$A$300,0,0,COUNTA(Sheet2!$A$2: $A$300),1)

This is a moving range. Now set your validation on sheet 1 to point to
=Test

This will work if you add or remove names from your validation list.
It assumes your validation list won't be longer than 300 rows.

Regards

Marcus

mikeb wrote:
Hi, sheet1 col A is filled with values, I want col B to have a validation
based on a named range on sheet2. The problem, # the number of rows that
have validaton in sheet1!col b should change based on how many rows have
values in col a, also, the actual list will change due to changes to the
named range. I thought of deleting the validation and recreating every time
the macro runs. Also, the code below added it, then I wrote some code to
delete it and now code that added it befare no longer does, why?

On Error Resume Next
ThisWorkbook.Names("temp").Delete
test = "$A$5:$A$" & intUniqueTrDesc + 4
Sheets(RT).Range(test).Name = "temp"

With Range(Cells(StartRow, pbeydescchg), Cells(intNumOfTrDesc + HdrRow,
pbeydescchg)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Temp"

.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With
On Error GoTo 0

Problem with validation deletion/modification
 

Hi Mike,

Just want to say Hi, and I was wondering how everything is going.
If anything is unclear, please let me know.
It is my pleasure to be of assistance.


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.