Take Action on Cells within Selection

[Mike G - D.C.]

Folks –
I'm looking for a way for the end-user to select a sub-set, more often
non-contiguous, of data within a worksheet to take action.

I have a worksheet that will contain data in columns A through Y for any
number of rows. My end-user will be instructed to ctrl+click on the row
number of each row they want to take action. After which, the user will
select a button that triggers a macro to hopefully execute on just the rows
they've selected.

One thought I have is to programmatically insert an "x" in column Z of each
row in the selection. The logic being that each macro would only apply to
rows with an "x" in column Z.

Examples of actions are, find/replace values within the selected range,
copy/paste selected rows into a new workbook and delete rows selected.

All suggestions are greatly appreciated.

 

[Dave Peterson]

I'd create a range of the first column ("A") used in the selected rows. Then
loop through that range. I could expand the single cell range by resizing it:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Try later" 'user hit cancel
Exit Sub
End If

Set myRng = Intersect(myRng.EntireRow, myRng.Parent.Columns(1))

MsgBox myRng.Address

For Each myCell In myRng.Cells
MsgBox myCell.Address & vbLf & myCell.Resize(1, 25).Address
Next myCell

End Sub

 

[Jim Thomlinson]

It is simpler than that...

Record a macro (anything will do) in order to create a standard code module
in the VBE. Go to the VBE and in the module add this code

Public Sub DoStuff()
Dim rng As Range

For Each rng In Selection
MsgBox rng.Address
Next rng
End Sub

Now add a command button from the forms toolbar and connect it to this
macro...

 

[Mike G - D.C.]

I really appreciate the suggestions and examples. This gives me something to
work with and try to understand.