Hi All.......
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious cells
with values to the right in the same row only. If there be a broken column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

This might be more than you need, but it assigns the name and then tests to
make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

Many thanks, kind Sir.........your code worked perfectly for me the first
try.

Merry Christmas to you and yours,

Vaya con Dios,
Chuck, CABGx3


"JLGWhiz" wrote in message
...
This might be more than you need, but it assigns the name and then tests
to make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

Well.........further testing shows that this code skips over empty cells and
includes all cells out to the last filled in the row. That's not exactly
the way I wanted it. I just wanted the contiguious ones from B1 rightward
to the first empty one.

Tks anyway,

Vaya con Dios,
Chuck, CABGx3




"JLGWhiz" wrote in message
...
This might be more than you need, but it assigns the name and then tests
to make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

Do you mean the first empty cell in a column to the right of B1 no matter
what row that cell is in (as long as it is not below the last piece of data
in Column B)?

Also, what is in your cells... data or formulas? If formulas, I guess if
they return the empty string (""), then you consider that to be an "empty
cell", right?

--
Rick (MVP - Excel)


"clr" wrote in message
...
Well.........further testing shows that this code skips over empty cells
and includes all cells out to the last filled in the row. That's not
exactly the way I wanted it. I just wanted the contiguious ones from B1
rightward to the first empty one.

Tks anyway,

Vaya con Dios,
Chuck, CABGx3




"JLGWhiz" wrote in message
...
This might be more than you need, but it assigns the name and then tests
to make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious
cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

I'm only interested in one row at a time. If I have values in B1, C1, D1
and nothing in E1, but continue with values in F1, etc. I want the macro to
create a NamedRange of B1:D1, regardless of the content of any other row.
The values in my cells are all numbers presently, but hopefully the macro
would also work with text values....no formulas now, but as you say "if
they return the empty string (""), then you consider that to be an "empty
cell".........that would be icing on the cake.

Tks for the interest
Vaya con Dios,
Chuck, CABGx3

"Extra points"...... if the macro would also return the Column number/letter
of the rightmost cell in the newly NamedRange.








"Rick Rothstein" wrote in message
...
Do you mean the first empty cell in a column to the right of B1 no matter
what row that cell is in (as long as it is not below the last piece of
data in Column B)?

Also, what is in your cells... data or formulas? If formulas, I guess if
they return the empty string (""), then you consider that to be an "empty
cell", right?

--
Rick (MVP - Excel)


"clr" wrote in message
...
Well.........further testing shows that this code skips over empty cells
and includes all cells out to the last filled in the row. That's not
exactly the way I wanted it. I just wanted the contiguious ones from B1
rightward to the first empty one.

Tks anyway,

Vaya con Dios,
Chuck, CABGx3




"JLGWhiz" wrote in message
...
This might be more than you need, but it assigns the name and then tests
to make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious
cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

If you only want the first group of filled cells on a row then:

Sub sl()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Range("B1", Range("B1").End(xlToRight)).Name = "myName"
MsgBox Range("myName").Address
End Sub



"clr" wrote in message
...
I'm only interested in one row at a time. If I have values in B1, C1, D1
and nothing in E1, but continue with values in F1, etc. I want the macro
to create a NamedRange of B1:D1, regardless of the content of any other
row. The values in my cells are all numbers presently, but hopefully the
macro would also work with text values....no formulas now, but as you say
"if they return the empty string (""), then you consider that to be an
"empty cell".........that would be icing on the cake.

Tks for the interest
Vaya con Dios,
Chuck, CABGx3

"Extra points"...... if the macro would also return the Column
number/letter of the rightmost cell in the newly NamedRange.








"Rick Rothstein" wrote in message
...
Do you mean the first empty cell in a column to the right of B1 no matter
what row that cell is in (as long as it is not below the last piece of
data in Column B)?

Also, what is in your cells... data or formulas? If formulas, I guess if
they return the empty string (""), then you consider that to be an "empty
cell", right?

--
Rick (MVP - Excel)


"clr" wrote in message
...
Well.........further testing shows that this code skips over empty cells
and includes all cells out to the last filled in the row. That's not
exactly the way I wanted it. I just wanted the contiguious ones from B1
rightward to the first empty one.

Tks anyway,

Vaya con Dios,
Chuck, CABGx3




"JLGWhiz" wrote in message
...
This might be more than you need, but it assigns the name and then
tests to make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with
values
therein starting at cell B1 and continuing through all contigunious
cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3

Now, that does it, with the greatest of cool..........thank you kind Sir!

Vaya con Dios,
Chuck, CABGx3



"JLGWhiz" wrote in message
...
If you only want the first group of filled cells on a row then:

Sub sl()
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Range("B1", Range("B1").End(xlToRight)).Name = "myName"
MsgBox Range("myName").Address
End Sub



"clr" wrote in message
...
I'm only interested in one row at a time. If I have values in B1, C1,
D1 and nothing in E1, but continue with values in F1, etc. I want the
macro to create a NamedRange of B1:D1, regardless of the content of any
other row. The values in my cells are all numbers presently, but
hopefully the macro would also work with text values....no formulas now,
but as you say "if they return the empty string (""), then you consider
that to be an "empty cell".........that would be icing on the cake.

Tks for the interest
Vaya con Dios,
Chuck, CABGx3

"Extra points"...... if the macro would also return the Column
number/letter of the rightmost cell in the newly NamedRange.








"Rick Rothstein" wrote in message
...
Do you mean the first empty cell in a column to the right of B1 no
matter what row that cell is in (as long as it is not below the last
piece of data in Column B)?

Also, what is in your cells... data or formulas? If formulas, I guess if
they return the empty string (""), then you consider that to be an
"empty cell", right?

--
Rick (MVP - Excel)


"clr" wrote in message
...
Well.........further testing shows that this code skips over empty
cells and includes all cells out to the last filled in the row. That's
not exactly the way I wanted it. I just wanted the contiguious ones
from B1 rightward to the first empty one.

Tks anyway,

Vaya con Dios,
Chuck, CABGx3




"JLGWhiz" wrote in message
...
This might be more than you need, but it assigns the name and then
tests to make sure that it took.

Sub dk()
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1", ActiveSheet.Cells(1, lc))
rng.Name = "myRange"
Range("myRange").Interior.ColorIndex = 3
End Sub




"CLR" wrote in message
...
Hi All.......
I need help please to create a Named Range of all the cells with
values
therein starting at cell B1 and continuing through all contigunious
cells
with values to the right in the same row only. If there be a broken
column,
then all cells to the right of that break need not be included.

Tks,
Vaya con Dios,
Chuck, CABGx3