PDA

View Full Version : How to set Range based off Target Range?



kend0g187
06-18-2018, 02:29 PM
I want to do something like this:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim affectedRange As Range
affectedRange = Target + 6 columns
End Sub


Here's an example of what I mean by "+ 6 columns": If the target range was C4, then affectedRange should be C4:I4. If target was C4:E8 the affectedRange should be C4:L8.

Paul_Hossler
06-18-2018, 02:42 PM
Private Sub Worksheet_Change(ByVal Target As Range)

Dim affectedRange As Range

Set affectedRange = Target.Resize(1,6)

End Sub

kend0g187
06-18-2018, 03:21 PM
Thanks! Although won't your code always produce a range with 1 row and 6 columns? The way that seems to work the way I wanted is this:

Set affectedRange = Target.Resize(, Target.Columns.Count + 6)

Paul_Hossler
06-18-2018, 04:01 PM
Yes - one row. Use your change





If target was C4:E8 the affectedRange should be C4:L8.

I think this should be C4:K8

kend0g187
06-18-2018, 06:55 PM
You're right. I don't know the alphabet, apparently.


I think this should be C4:K8

Paul_Hossler
06-19-2018, 03:37 AM
That's OK

I can't count