User input to delete value in named range:

[mykal66]

Hi

I use this piece of code to allow a user to add additional values to a named range via an input box. The range is then updated to include the additional value. I am leaving my current job soon so would like colleagues who don't know how to do this manually to be able to do the reverse as well if possible but can't figure out how to change this code to do the opposite or find anything on the internet. i.e. look for name entered into the input box, delete the name if it exists, shift names up so there are no blanks (not delete whole row) the amend the range of cells associated with the name range.

Any help really appreciated as always

Private Sub CommandButton20_Click()
Dim strName As String
Dim Result
Dim newEntry As Variant, rws As Long, adr As String
Unload Me

    strName = InputBox(Prompt:="Add Staff Member", _
          Title:="ADD STAFF MEMBER", Default:="Enter Staff Member Here!")
          If strName = "Enter Staff Member Here!" Or _
          strName = vbNullString Then
          Exit Sub
Else
   
    On Error Resume Next
    Result = WorksheetFunction.Match(strName, Range("Staff"), False)
    If Err = 0 Then
        MsgBox "The member of staff you have entered already exists"
Else

newEntry = strName
rws = Range("Staff").Rows.Count
Range("Staff").Cells(1, 1).Offset(rws, _
    0).Value = newEntry
With Range("Staff").Name
    adr = .RefersTo
    .Delete
End With
Range(adr).Resize(rws + 1, 1).Name = "Staff"
  Err.Clear
  MsgBox "New member of staff  has been added to list"
  End If
    On Error GoTo 0
   
  End If
  
End Sub

[patel]

Private Sub CommandButton21_Click()
Dim strName As String
Dim Result
Dim newEntry As Variant, rws As Long, adr As String
Unload Me
    strName = InputBox(Prompt:="Delete Staff Member", _
          Title:="DELETE STAFF MEMBER", Default:="Enter Staff Member Here!")
          If strName = "Enter Staff Member Here!" Or _
          strName = vbNullString Then
          Exit Sub
Else
   
    On Error Resume Next
    Result = WorksheetFunction.Match(strName, Range("Staff"), False)
    If Result = 0 Then
        MsgBox "The member of staff you have entered does not exist"
        Exit Sub
Else
Range("Staff").Cells(Result, 1).Delete
 MsgBox "Member deleted"
  End If
    On Error GoTo 0
   
  End If
  
End Sub

[Bob Phillips]

This seems tremendously convoluted to me, why not just use a dynamic named range?

[patel]

can you show us how create dinamic range and code changes ?

[Bob Phillips]

You create a named range that is dynamic in the number of rows and columns, that way if data is deleted, the range flexes accordingly.

For instance, say you have a range that starts in A1, and finishes in M20. Using an OFFSET dynamic named range, you would define it as

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

and that would point at A1:M20. Delete row 5 for instance, and it would automatically adjust to point at A1:M19.

[mykal66]

Patel - This is great, thanks you very much. I've tested and it works perfectly

[mykal66]

Hi again XLD. Because of issues in the past with colleagues deleting formulas or values i use hidden sheet a lot of the time so use this method to allow the users to amend. This way they have to think about what they are doing to add or delete and cant' delete anything in error. I will however give your method a go as well and thank you for offering a different method which i will try too - love this stuff and never get bored playing with new ways of doing things. Thank you again

[patel]

@xld
thanks for suggestion, can the range have numbers and texts ?

[Bob Phillips]

Yes, COUNTA counts numbers and non-numbers.