ExcelBanter - Need help with named ranges

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Need help with named ranges (https://www.excelbanter.com/excel-programming/372083-need-help-named-ranges.html)

Trefor

Need help with named ranges

The below code works, but I need to be able to select the sheet first. Is it
possible to do this with selecting the sheet, which in my case is (Visible =
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

Bob Phillips

Need help with named ranges

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet first. Is
it
possible to do this with selecting the sheet, which in my case is (Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

Trefor

Need help with named ranges

Bob,

Perfect, many thanks.

--
Trefor

"Bob Phillips" wrote:

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet first. Is
it
possible to do this with selecting the sheet, which in my case is (Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

Trefor

Need help with named ranges

Bob,

I just found a catch, if the cell contains a formula that resolves to a
blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the
cells containing a formula. I am trying to capture only the cells that have a
value other than "", is this possible?

--
Trefor

"Trefor" wrote:

Bob,

Perfect, many thanks.

--
Trefor

"Bob Phillips" wrote:

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet first. Is
it
possible to do this with selecting the sheet, which in my case is (Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

Bob Phillips

Need help with named ranges

Trefor,

Do you mean that it goes to the end of the column? If so, if say
Data_EMCSoftware is blank, what should be selected/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
Bob,

I just found a catch, if the cell contains a formula that resolves to a
blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the
cells containing a formula. I am trying to capture only the cells that
have a
value other than "", is this possible?

--
Trefor

"Trefor" wrote:

Bob,

Perfect, many thanks.

--
Trefor

"Bob Phillips" wrote:

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet
first. Is
it
possible to do this with selecting the sheet, which in my case is
(Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

Dave Peterson

Need help with named ranges

I'd just look.

Dim DestCell as Range
....
With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
set DestCell = rng.end(xldown)
do
if destcell.row = rng.row then
'stop looking, at the top of the range
exit do
end if
if destcell < "" then
exit do
else
'come up a row and continue testing
set destcell = destcell.offset(-1,0)
end if
loop
.Range(rng, destcell)).Name = "Data_EMCSoftwareList"
.....

Trefor wrote:

Bob,

I just found a catch, if the cell contains a formula that resolves to a
blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the
cells containing a formula. I am trying to capture only the cells that have a
value other than "", is this possible?

--
Trefor

"Trefor" wrote:

Bob,

Perfect, many thanks.

--
Trefor

"Bob Phillips" wrote:

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet first. Is
it
possible to do this with selecting the sheet, which in my case is (Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

--

Dave Peterson

Trefor

Need help with named ranges

Dave,

Sorry for the delay in getting back to you. This worked a treat thankyou.

--
Trefor

"Dave Peterson" wrote:

I'd just look.

Dim DestCell as Range
....
With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
set DestCell = rng.end(xldown)
do
if destcell.row = rng.row then
'stop looking, at the top of the range
exit do
end if
if destcell < "" then
exit do
else
'come up a row and continue testing
set destcell = destcell.offset(-1,0)
end if
loop
.Range(rng, destcell)).Name = "Data_EMCSoftwareList"
.....

Trefor wrote:

Bob,

I just found a catch, if the cell contains a formula that resolves to a
blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the
cells containing a formula. I am trying to capture only the cells that have a
value other than "", is this possible?

--
Trefor

"Trefor" wrote:

Bob,

Perfect, many thanks.

--
Trefor

"Bob Phillips" wrote:

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet first. Is
it
possible to do this with selecting the sheet, which in my case is (Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

--

Dave Peterson

Trefor

Need help with named ranges

Bob,

Thankyou for your reply, Dave nailed it in his reply.

--
Trefor

"Bob Phillips" wrote:

Trefor,

Do you mean that it goes to the end of the column? If so, if say
Data_EMCSoftware is blank, what should be selected/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
Bob,

I just found a catch, if the cell contains a formula that resolves to a
blank the .Range(rng, rng.End(xlDown)).Name still runs to the end of the
cells containing a formula. I am trying to capture only the cells that
have a
value other than "", is this possible?

--
Trefor

"Trefor" wrote:

Bob,

Perfect, many thanks.

--
Trefor

"Bob Phillips" wrote:

Dim rng As Range

With Workbooks(WorkbookMain).Worksheets("sheetname")
Set rng = .Range("Data_EMCSoftware")
.Range(rng, rng.End(xlDown)).Name = "Data_EMCSoftwareList"

Set rng = .Range("Data_SoftwareExclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareExludeList"

Set rng = .Range("Data_SoftwareInclude")
.Range(rng, rng.End(xlDown)).Name = "Data_SoftwareIncludeList"

End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trefor" wrote in message
...
The below code works, but I need to be able to select the sheet
first. Is
it
possible to do this with selecting the sheet, which in my case is
(Visible
=
False)

Sheets("sheetname").Select
With Workbooks(WorkbookMain).Sheets("sheetname")
.Range("Data_EMCSoftware").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_EMCSoftwareList"

.Range("Data_SoftwareExclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareExludeList"

.Range("Data_SoftwareInclude").Select
.Range(Selection, Selection.End(xlDown)).Select
Selection.name = "Data_SoftwareIncludeList"

End With
--
Trefor

All times are GMT +1. The time now is 10:13 AM.