VBA for adding and removing a name from a named range (I think I'm halfway there)

[grf]

Hi Experts,
On my DATA Sheet, clients’ names are listed on Rows 3-52 on Column B. The Double Click macro below allows me to copy a Client’s name from column B and add it to the bottom of a Dynamic Named Range entitled “cashdd” which starts on Row 87 of Column A and records the transaction with a tick in the relevant cell in Column G. At present a Double Click a on a ticked cell in G Rows 3-52 will remove the tick and I wondered if there was any way of removing the name from “cashdd” as well

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("G3:G52")) Is Nothing Then
Cancel = True
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"

If Target = "a" Then Target.Offset(, -5).Copy Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
Target = vbNullString

End If
End If
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("G3:G52")) Is Nothing Then
Cancel = True
On Error GoTo M
Dim SearchString As String
Dim SearchRange As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
    If Target = "a" Then Target.Offset(, -5).Copy Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1)
        Else
        Target = vbNullString
        SearchString = Target.Offset(, -5).Value
        Set SearchRange = Range("A1:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        SearchRange.Value = ""
    End If
End If
Exit Sub
M: MsgBox "No value found"
End Sub

 

[My Aswer Is This]

Or this:
Previous script cleared the value in column "A" when check was cleared in column "G"
This script will delete the cell and shift up so no empty cells.
Use which ever is best for you:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("G3:G52")) Is Nothing Then
'Modified 7-22-17 3:00 PM EDT
Cancel = True
On Error GoTo M
Dim SearchString As String
Dim SearchRange As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
    If Target = "a" Then Target.Offset(, -5).Copy Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1)
        Else
        Target = vbNullString
        SearchString = Target.Offset(, -5).Value
        Set SearchRange = Range("A1:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        SearchRange.Delete xlShiftUp
    End If
End If
Exit Sub
M: MsgBox "No value found"
End Sub

 

[grf]

My Aswer Is This,
I thought this was going to be so difficult that I was reluctant to ask about moving the list up to avoid gaps. That was going to be my next question. Well, not only did your solution work first time straight out of the box but you have anticipated my next question and nailed that too. Thank you very much My Aswer Is This, I’m very grateful, I didn’t know about SearchString and SearchRange and would never have been able to achieve this on my own. Thanks again,
Regards grf

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

My Aswer Is This,
I thought this was going to be so difficult that I was reluctant to ask about moving the list up to avoid gaps. That was going to be my next question. Well, not only did your solution work first time straight out of the box but you have anticipated my next question and nailed that too. Thank you very much My Aswer Is This, I’m very grateful, I didn’t know about SearchString and SearchRange and would never have been able to achieve this on my own. Thanks again,
Regards grf