Loop through range but act only on cells that are selected in that range

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Say in Range("A1:A25") (named range or not) I want to loop through that range but only enforce code on cells that have been selected prior to running my loop.

Something like

Dim c as range
Dim cV as string

For each c in Range("A1:A25")
with c.selection
cV = c.value
do something with cV
end with
Next

The range is a list of names of worksheets.
If that name is selected then cV.Delete

Thanks,
Howard
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Tried to edit, but took too long I suppose...

I duplicated the scenerio and came up with this... Be careful, because it will delete worksheets without warning, so make sure you have a backup.

Code:
Sub delsht()

Dim c As Range

For Each c In Selection
    Application.DisplayAlerts = False
    Sheets(c.Value).Delete
    Application.DisplayAlerts = True
Next

End Sub
 
Upvote 0
Hmmm, you know that makes sense. I'll give that a try, somehow I wouldn't let go of the idea of having to define a range to do a loop.

Thanks,
Howard
 
Upvote 0
Hi rj,

I'll play with that 'lil snippet.

I was thinking c.value would have to be told it was a worksheet.

Thanks,
Howard
 
Upvote 0
I was thinking c.value would have to be told it was a worksheet.

and it is being told, since it is the worksheet's name. Just putting it in Sheets(c.value) tells it that it's a worksheet.

You could take it one step further if you absolutely wanted to make sure you stayed within the range you want, you could add this..... Watch the addition of a function....

Code:
Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2'
Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function
Sub delsht()

Dim c As Range

For Each c In Selection
    If InRange(c, Range("A1:A25")) Then
        Application.DisplayAlerts = False
        Sheets(c.Value).Delete
        Application.DisplayAlerts = True
    End If
Next

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top