Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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").Resiz e(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Named Ranges Help
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").Resiz e(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Named Ranges Help
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").Resiz e(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Resize Dynamic Ranges | Excel Programming | |||
Resize Dynamic Ranges | Excel Programming | |||
Resize Dynamic Ranges | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming |