Name a portion of a range with VBA

ilya2004

Board Regular
Joined
Mar 17, 2011
Messages
135
Hi,
I am sure this is a simple quesiton, but I am not sure how to word it correctly to get it to show up in a Google search.

I am trying to refer to a portion of named range via VBA. So for example, I'll have a column named "MyColumn" and I only want to select rows 8-41, how would I write this correctly:

Code:
range("MyColumn").Cells(8,1):Cells(41:,1)

Thanks!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You could use:
Code:
range("MyColumn").Cells(8,1).Resize(34)
for example.
 
Upvote 0
I'm not sure how to apply this. If I tried range("MyColumn").Cells(8,1).Resize(34).Select, I get an error
 
Last edited:
Upvote 0
Yes, you can use normal range syntax such as:
Code:
With range("MyColumn")
  msgbox .Range(.Cells(8,1), .Cells(41,1)).address
End With
 
Upvote 0
Great, I was able to get the second one to work, I'll still have to play around with the first one, thank you!
 
Upvote 0

Forum statistics

Threads
1,226,498
Messages
6,191,377
Members
453,655
Latest member
lasvegasbuffet

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