Hyperlink to delete column

[happyhungarian]

Hi, I was hoping to figure out a way to easily add a "delete column" function to my worksheet. I was going to have, in row 2 of each column, a hyperlink that's mapped to (what i'm hoping to be) the same macro. That macro would recognize what cell the clicked hyperlink is sitting in and then proceed to delete the entire column that's associated with that cell. Hope that makes sense!!!

 

[cooper645]

What about putting this in the Sheet module,

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A2:M2")) Is Nothing Then
            ActiveCell.EntireColumn.Delete (xlLeft)
        End If
    End If
End Sub

If need be, entering the delete info could be done via a macro such as the following

Option Explicit


Sub AddDelInfo()
ActiveSheet.Range("A2:M2").Value = "Click to Delete Column"
End Sub

Obviously, amend the ranges as per your needs, or even use clear contents instead of entirecolumn.delete as a possibility

 

[happyhungarian]

Here's an example... the "Delete" you see in each column would all be linked to the same macro. If the link in G1 is clicked then all of column G is deleted

	A	B	C	D	E	F	G	H
1	Delete	Delete	Delete	Delete	Delete	Delete	Delete	Delete
2	Task 1	Task 2	Task 3	Task 4	Task 5	Task 6	Task 7	Task 8
3

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>

 

[cooper645]

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A1:M1")) Is Nothing Then
            ActiveCell.EntireColumn.Delete (xlLeft)
        End If
    End If 

End Sub

Just add this VBA to your sheet module, and amend the M to whichever is your last column of data.
When ever the user clicks a cell in row 1 that column will be deleted.

If you wish I can amend it to ask the user if they're sure they want to delete the column. Just let me know.

 

[Fluff]

Alternately you could use a double-click event

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Row = 1 Then Target.EntireColumn.Delete
   Cancel = True
End Sub

Double-click any cell in row 1 & that column will be deleted.

 

[cooper645]

I took the liberty to write in a user check before deleting just incase you or anyone searching for this in future needs it.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ans As Integer
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A1:M1")) Is Nothing Then
            Ans = MsgBox("Are you sure you wish to delete column " & Chr(ActiveCell.Column + 64), vbYesNo + vbExclamation, "Delete Column?")
                If Ans = vbNo Then
                    ActiveCell.Offset(1).Select
                    Exit Sub
                Else
                    ActiveCell.EntireColumn.Delete (xlLeft)
                End If
        End If
    End If
End Sub

 

[happyhungarian]

Beautiful!!! Thank you!

 

[cooper645]

if you wanted

your post here. https://www.mrexcel.com/forum/excel-questions/1092186-copying-inserting-column.html

could be added into this macro as per:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Ans As Integer, [COLOR=#ff0000]LastCol As Long[/COLOR]


[COLOR=#ff0000]LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column - 1[/COLOR]


    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A1:M1")) Is Nothing Then
            Ans = MsgBox("Are you sure you wish to delete column " & Chr(ActiveCell.Column + 64), vbYesNo + vbExclamation, "Delete Column?")
                If Ans = vbNo Then
                    ActiveCell.Offset(1).Select
                    Exit Sub
                Else
                    ActiveCell.EntireColumn.Delete (xlLeft)
                    [COLOR=#ff0000]Columns(LastCol).Copy Columns(LastCol + 1)[/COLOR]
                End If
        End If
    End If
End Sub