Solved: Double click to delete as shift column

[slamet Harto]

Hi guys,

just wondering, I want to delete (delete as shift column) a value in a sheet with double click.

example:
sheet main cell d7 will be deleted a "A7" value in sheet aaa.
Can you show how to solve it?

Many thanks in advance
Rgds, Harto

[Bob Phillips]

Can you explain that another way, I don't get what you want.

[georgiboy]

From what i understand this is what he wants.

When you double click cell "D7" in sheet "Main" which contains the value "A7" he wants it to delete and shift up that value from the sheet named in the adjacent cell to "D7" in sheet "Main".

Phew!

[georgiboy]

Place this in worksheet "Main"

[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyRange As Range, rCell As Range, MainV As String

MainV = ActiveCell.Value

If ActiveCell = "" Then Exit Sub

Set MyRange = Sheets(ActiveCell.Offset(, 1).Value).Range("A1:B1000")

For Each rCell In MyRange.Cells

If rCell.Value = MainV Then

rCell.Delete xlUp

End If

Next rCell
End Sub[/VBA]

its a bit rough but you should get the idea

hope this helps

[slamet Harto]

Hi BoB

Georgiboy statement is correct. I wanted to delete as shift column for a value in cell D7 in sheet Main.
In this case, the value is "A7" in sheet "aaa". user have to double click on the cell D7 to delete it.

Can you help me, please?

[georgiboy]

Have you tried putting my above code in the worksheet main and double clicking te cell in question.

[georgiboy]

I have posted an example sheet for you.

[slamet Harto]

Dear Georgiboy,

Apologize me, your code is work fine with a bit modified.
Thanks for assistance and highly appreciate it.

Best,
Harto