Using VB to to create named ranges

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

Hi,

This is a two part question that may have one answer.

1. I'm using the following code to create a named range of **everything** on
a worksheet:

Sub CreateRangeName()

Dim first_row As Integer
Dim first_col As Integer
Dim num_rows As Integer
Dim num_cols As Integer
Dim rng As String

With ActiveSheet
first_row = .UsedRange.Row
first_col = .UsedRange.Column
num_rows = .UsedRange.Rows.Count
num_cols = .UsedRange.Columns.Count
End With

rng = "R" & first_row & "C" & first_col & ":R" & num_rows & "C" & num_cols

ActiveWorkbook.Names.Add Name:="RangeName", RefersToR1C1:="=" & rng

End Sub

This works fine on a worksheet where I want to select everything but there
maybe occasion where I want to **leave out a section** from the range. Is
there a better way I could code this so I can be more specific about the
range I want to name but without hardcoding cell addresses.

Thanks
 
How about something like

Set rng = Range("A1:A10, B5:B8,H5:H18, M5:O10")
rng.Name = "RangeName"
 
Hi Bob,

Thanks for the reply.

The only problem here is the range is hardcode. I can afford to hardcode the
starting cell of the range but after that the range might change daily.
xlright and xlend won't work from the starting point (Say Cell A2) as these
functions might not cover that required range - I'll layout an example

A B C D
1
2 Blah1 Blah2 Blah3
3 1234 1234 1234 1234
4 1234 1234 1234 1234
5 1234 1234

This range could go beyound Row 5 and Col D do hard coding the range is not
an option

Thanks agin for your input

Paul
 
Hi Paul,

Are you wanting to reference A1:C5 in its totality, accepting that it may
extend to say A1:H6, or are you wanting just to reference A2:C2, A3:D3,
A4:D4, B5:C5?

If it is the latter, can I ask why, as the others are I presume empty, so
you can manage that in the code?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete Method of Range object doesn't work in VB script 1
Copy filtered data to sheet 2 1
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
range problem 2
Creating a named range 4
Named Range maker code 9
InputBox to name a range 2
Copy a range data from 1 sheet to multiple selected sheets 1

Back
Top