Resize Named Ranges Help

Greetings,

I use the following code to automatically resize a named worksheet range of
contiguous data that is in one column and grows vertically.

With Worksheets("Sheetname").Range("WorkingData").Resize(1, 1)
.Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
.End(xlUp)).Name = "WorkingData"
End With

It was published in this group and has proven very useful.Unfortunately I
can't seem to write code that is this compact or find any references how you
combine Resize and various scenarios.

1. With the assumptions that the named worksheet range of contiguous data is
in multiple columns and grows vertically, how would you alter the code?
2. With the assumptions that the named worksheet range of non-contiguous
data is in multiple columns and grows vertically, how would you alter the
code?
3. With the assumptions that the named worksheet range of contiguous data is
in multiple columns and grows horizontally, how would you alter the code?
4. With the assumptions that the named worksheet range of non-contiguous
data is in multiple columns and grows horizontally, how would you alter the
code?
5. With the assumptions that the named worksheet range of contiguous data is
in multiple columns and grows vertically and horizontally, how would you
alter the code?
6. With the assumptions that the named worksheet range of non-contiguous
data is in multiple columns and grows vertically and horizontally, how would
you alter the code?

Thank you in advance for your help!!

Ray

When I have trouble seeing what's going on, I'll use multiple statements.


dim TopCell as range
dim BotCell as range
With worksheets("sheetname")
set topcell = .range("workingdata").cells(1,1) 'same as .resize(1,1)
set botcell = .cells(.rows.count,topcell.column).end(xlup)
.range(topcell,botcell).name = "WorkingData"
end with

Same kind of thing when you extend to the right.

As for noncontiguous areas, I think I'd break it down into each area and then
use Union to put it all together.

dim TopCell as Range
dim BotCell as range
Dim AllRng as range
dim myArea as range

with worksheets("sheetname")
for each myarea in .range("workingdata").areas
set topcell = myarea.cells(1,1) 'same as .resize(1,1)
set botcell = .cells(.rows.count,topcell.column).end(xlup)
if allrng is nothing then
set allrng = .range(topcell,botcell)
else
set allrng = union(allrng,.range(topcell,botcell)
end if
next myarea
end with

if allrng is nothing then
'something very bad happened!
else
allrng.name = "workingdata"
end if

This picks out the first column of each area and extends that column--the
resulting range may not have the same number of rows in each area.


Ray Batig wrote:
>
> Greetings,
>
> I use the following code to automatically resize a named worksheet range of
> contiguous data that is in one column and grows vertically.
>
> With Worksheets("Sheetname").Range("WorkingData").Resize(1, 1)
> .Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
> .End(xlUp)).Name = "WorkingData"
> End With
>
> It was published in this group and has proven very useful.Unfortunately I
> can't seem to write code that is this compact or find any references how you
> combine Resize and various scenarios.
>
> 1. With the assumptions that the named worksheet range of contiguous data is
> in multiple columns and grows vertically, how would you alter the code?
> 2. With the assumptions that the named worksheet range of non-contiguous
> data is in multiple columns and grows vertically, how would you alter the
> code?
> 3. With the assumptions that the named worksheet range of contiguous data is
> in multiple columns and grows horizontally, how would you alter the code?
> 4. With the assumptions that the named worksheet range of non-contiguous
> data is in multiple columns and grows horizontally, how would you alter the
> code?
> 5. With the assumptions that the named worksheet range of contiguous data is
> in multiple columns and grows vertically and horizontally, how would you
> alter the code?
> 6. With the assumptions that the named worksheet range of non-contiguous
> data is in multiple columns and grows vertically and horizontally, how would
> you alter the code?
>
> Thank you in advance for your help!!
>
> Ray

--

Dave Peterson

I didn't test any of that code and missed a closing paren with this line:
Set AllRng = Union(AllRng, .Range(TopCell, BotCell))

Dave Peterson wrote:
>
> When I have trouble seeing what's going on, I'll use multiple statements.
>
> dim TopCell as range
> dim BotCell as range
> With worksheets("sheetname")
> set topcell = .range("workingdata").cells(1,1) 'same as .resize(1,1)
> set botcell = .cells(.rows.count,topcell.column).end(xlup)
> .range(topcell,botcell).name = "WorkingData"
> end with
>
> Same kind of thing when you extend to the right.
>
> As for noncontiguous areas, I think I'd break it down into each area and then
> use Union to put it all together.
>
> dim TopCell as Range
> dim BotCell as range
> Dim AllRng as range
> dim myArea as range
>
> with worksheets("sheetname")
> for each myarea in .range("workingdata").areas
> set topcell = myarea.cells(1,1) 'same as .resize(1,1)
> set botcell = .cells(.rows.count,topcell.column).end(xlup)
> if allrng is nothing then
> set allrng = .range(topcell,botcell)
> else
> set allrng = union(allrng,.range(topcell,botcell)
> end if
> next myarea
> end with
>
> if allrng is nothing then
> 'something very bad happened!
> else
> allrng.name = "workingdata"
> end if
>
> This picks out the first column of each area and extends that column--the
> resulting range may not have the same number of rows in each area.
>
> Ray Batig wrote:
> >
> > Greetings,
> >
> > I use the following code to automatically resize a named worksheet range of
> > contiguous data that is in one column and grows vertically.
> >
> > With Worksheets("Sheetname").Range("WorkingData").Resize(1, 1)
> > .Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
> > .End(xlUp)).Name = "WorkingData"
> > End With
> >
> > It was published in this group and has proven very useful.Unfortunately I
> > can't seem to write code that is this compact or find any references how you
> > combine Resize and various scenarios.
> >
> > 1. With the assumptions that the named worksheet range of contiguous data is
> > in multiple columns and grows vertically, how would you alter the code?
> > 2. With the assumptions that the named worksheet range of non-contiguous
> > data is in multiple columns and grows vertically, how would you alter the
> > code?
> > 3. With the assumptions that the named worksheet range of contiguous data is
> > in multiple columns and grows horizontally, how would you alter the code?
> > 4. With the assumptions that the named worksheet range of non-contiguous
> > data is in multiple columns and grows horizontally, how would you alter the
> > code?
> > 5. With the assumptions that the named worksheet range of contiguous data is
> > in multiple columns and grows vertically and horizontally, how would you
> > alter the code?
> > 6. With the assumptions that the named worksheet range of non-contiguous
> > data is in multiple columns and grows vertically and horizontally, how would
> > you alter the code?
> >
> > Thank you in advance for your help!!
> >
> > Ray
>
> --
>
> Dave Peterson

--

Dave Peterson