Pass what column selected to another procedure

[FryGirl]

I have many different macros doing different actions within a spreadsheet by the user selecting the column first and the macro takes action on that column.

Example:

Sub CopyColumnOfData()
    Dim r As Range: Set r = Application.InputBox("Click in the column to filter by", Type:=8)
    Dim iCol As Long: iCol = r.Column
    Columns(iCol).Copy Range("C1")
End Sub

Sub DeleteColumnOfData()
    Dim r As Range: Set r = Application.InputBox("Click in the column to filter by", Type:=8)
    Dim iCol As Long: iCol = r.Column
    Columns(iCol).Delete
End Sub

How can I use the application.InputBox as its own macro and then pass the iCol variable to the copy and/or delete procedures?

 

[dmt32]

Hi,
If you want to share it for different procedures then maybe create a function

Function GetColumn() As Variant
    On Error Resume Next
    Set GetColumn = Application.InputBox("Click in the column to filter by", Type:=8)
    If Not GetColumn Is Nothing Then GetColumn = CLng(GetColumn.Column)
    On Error GoTo 0
End Function

Your codes:

Sub CopyColumnOfData()
    Dim iCol As Variant
    iCol = GetColumn
'cancel pressed
    If iCol = False Then Exit Sub
    Columns(iCol).Copy Range("C1")
End Sub




Sub DeleteColumnOfData()
    Dim iCol As Variant
    iCol = GetColumn
'cancel pressed
    If iCol = False Then Exit Sub
    Columns(iCol).Delete
End Sub

You will note that I changed iCol data type to Variant as Cancel button returns False.

If idea does what you want you could add an argument to Function to give ability for it to return either the selected Column or Row if needed for other requirements.

Hope Helpful

Dave

 

[FryGirl]

Thanks Dave. This is indeed very helpful.

 

[dmt32]

Thanks Dave. This is indeed very helpful.

Most welcome - many thanks for feedback

Dave