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 |
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 |
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 |
All times are GMT +1. The time now is 08:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com