Remove data from a [hidden] list

Jagwarman
Jagwarman used Ask the Experts™
on
I am using the below to add to a hidden list. Could a Expert provide me with VBA code that will enable data to be removed from the list.

So lets say in A30 it has Yellow and B30 Blue

If I want to remove this the a pop-up that asks for what is in column A so if I put Yellow it will remove data from A30 and B30 and then shift those cells up but not affect any other columns.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
forgot to put the code I am using:

With Sheets("Static")
    .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row+1).Value = InputBox("Please enter a value for cell A" & .Range("A" & Rows.Count).End(xlUp).Row+1)
    .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row+1).Value = InputBox("Please enter a value for cell B" & .Range("B" & Rows.Count).End(xlUp).Row+1)
End With
Top Expert 2016

Commented:
Hi,

pls try

With Sheets("Static")
    strFind = InputBox("Please enter a value to delete in Column A")
    Set f = .Range("A:A").Find(strFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not f Is Nothing Then
        f.EntireRow.Delete
    Else
        MsgBox strFind & " not found"
    End If
End With

Open in new window

Regards

Author

Commented:
Hi Rgonzo, thanks only thing is I only want it to delete A and B as mentioned in original request.....remove data from A30 and B30 and then shift those cells up but not affect any other columns.


Thanks
Top Expert 2016
Commented:
then try

With Sheets("Static")
    strFind = InputBox("Please enter a value to delete in Column A")
    Set f = .Range("A:A").Find(strFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If Not f Is Nothing Then
        f.Resize(, 2).Delete Shift:=xlUp
    Else
        MsgBox strFind & " not found"
    End If
End With

Open in new window

Author

Commented:
excellent many thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial