Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Any way to do this with a range?
I create a spreadsheet using code. Several columns are formatted with Wrap
Text on, so if there is a lot of text, the row expands to handle it. If there isn't, though, I wind up with some comparatively skinny rows. I would like to set all my rows heights to at least 25.5; if, however, text in the row is making row height greater then 25.5, don't touch it. (Several answers to the first post gave me code to set every row to 25.5. Thank you, but that will set expanded too narrow to read all the text inside.) At the moment, the only way I can think of to accomplish this is by iterating through every row using the code below. That's a lot of time over several thousand rows. Is there a faster way to accomplish this using a range? Ed For iRow = numRow To 2 Step -1 If Rows(iRow).RowHeight < 25.5 Then Rows(iRow).RowHeight = 25.5 End If Next iRow |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Any way to do this with a range?
Ed
How is the data getting into Excel? If you set all the row heights to 25.5 before the data is entered, then only those rows that need it should expand. I didn't test that though. If you can't set the row height before the data, then I think you're stuck with iterating through them. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... I create a spreadsheet using code. Several columns are formatted with Wrap Text on, so if there is a lot of text, the row expands to handle it. If there isn't, though, I wind up with some comparatively skinny rows. I would like to set all my rows heights to at least 25.5; if, however, text in the row is making row height greater then 25.5, don't touch it. (Several answers to the first post gave me code to set every row to 25.5. Thank you, but that will set expanded too narrow to read all the text inside.) At the moment, the only way I can think of to accomplish this is by iterating through every row using the code below. That's a lot of time over several thousand rows. Is there a faster way to accomplish this using a range? Ed For iRow = numRow To 2 Step -1 If Rows(iRow).RowHeight < 25.5 Then Rows(iRow).RowHeight = 25.5 End If Next iRow |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Any way to do this with a range?
Thanks for the reply, Dick. The spreadsheet is an index of reports being
processed. As each report is opened for processing, the code reads a number of text strings and writes these strings into a "template" Excel file. If I set the heights prior to writing the text, the text is truncated; both the height and width are set, and text overflowing those borders doesn't show. I wish there was a row height setting of "at least" (like there is with Word tables). BTW, I've got XL2k, if that makes a difference. Ed "Dick Kusleika" wrote in message ... Ed How is the data getting into Excel? If you set all the row heights to 25.5 before the data is entered, then only those rows that need it should expand. I didn't test that though. If you can't set the row height before the data, then I think you're stuck with iterating through them. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... I create a spreadsheet using code. Several columns are formatted with Wrap Text on, so if there is a lot of text, the row expands to handle it. If there isn't, though, I wind up with some comparatively skinny rows. I would like to set all my rows heights to at least 25.5; if, however, text in the row is making row height greater then 25.5, don't touch it. (Several answers to the first post gave me code to set every row to 25.5. Thank you, but that will set expanded too narrow to read all the text inside.) At the moment, the only way I can think of to accomplish this is by iterating through every row using the code below. That's a lot of time over several thousand rows. Is there a faster way to accomplish this using a range? Ed For iRow = numRow To 2 Step -1 If Rows(iRow).RowHeight < 25.5 Then Rows(iRow).RowHeight = 25.5 End If Next iRow |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Any way to do this with a range?
Ed
That stinks. Unless you can set the row height as you're writing the values (i.e. you're already looping through the cells to write the values and you set the row height at the same time), I think you're stuck looping through them to set the row height. If you can do at the same time, you would save one loop, but still not a very elegant solution. Dick "Ed" wrote in message ... Thanks for the reply, Dick. The spreadsheet is an index of reports being processed. As each report is opened for processing, the code reads a number of text strings and writes these strings into a "template" Excel file. If I set the heights prior to writing the text, the text is truncated; both the height and width are set, and text overflowing those borders doesn't show. I wish there was a row height setting of "at least" (like there is with Word tables). BTW, I've got XL2k, if that makes a difference. Ed "Dick Kusleika" wrote in message ... Ed How is the data getting into Excel? If you set all the row heights to 25.5 before the data is entered, then only those rows that need it should expand. I didn't test that though. If you can't set the row height before the data, then I think you're stuck with iterating through them. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... I create a spreadsheet using code. Several columns are formatted with Wrap Text on, so if there is a lot of text, the row expands to handle it. If there isn't, though, I wind up with some comparatively skinny rows. I would like to set all my rows heights to at least 25.5; if, however, text in the row is making row height greater then 25.5, don't touch it. (Several answers to the first post gave me code to set every row to 25.5. Thank you, but that will set expanded too narrow to read all the text inside.) At the moment, the only way I can think of to accomplish this is by iterating through every row using the code below. That's a lot of time over several thousand rows. Is there a faster way to accomplish this using a range? Ed For iRow = numRow To 2 Step -1 If Rows(iRow).RowHeight < 25.5 Then Rows(iRow).RowHeight = 25.5 End If Next iRow |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Any way to do this with a range?
Well, thanks for trying, Dick. What I've been doing is after writing
everything (14,000+ rows at last count), is doing AutoFit over the entire range; but since that makes some rows very small, I looped back through and reset the smaller ones to a greater height for easier reading. I'll play with your suggestion: while I'm on that row, Auto fit and, if less than, set bigger. It might take a bit more time on that iteration, but it might save more time on an overall loop-through. I appreciate the help. Have a good one. Ed "Dick Kusleika" wrote in message ... Ed That stinks. Unless you can set the row height as you're writing the values (i.e. you're already looping through the cells to write the values and you set the row height at the same time), I think you're stuck looping through them to set the row height. If you can do at the same time, you would save one loop, but still not a very elegant solution. Dick "Ed" wrote in message ... Thanks for the reply, Dick. The spreadsheet is an index of reports being processed. As each report is opened for processing, the code reads a number of text strings and writes these strings into a "template" Excel file. If I set the heights prior to writing the text, the text is truncated; both the height and width are set, and text overflowing those borders doesn't show. I wish there was a row height setting of "at least" (like there is with Word tables). BTW, I've got XL2k, if that makes a difference. Ed "Dick Kusleika" wrote in message ... Ed How is the data getting into Excel? If you set all the row heights to 25.5 before the data is entered, then only those rows that need it should expand. I didn't test that though. If you can't set the row height before the data, then I think you're stuck with iterating through them. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... I create a spreadsheet using code. Several columns are formatted with Wrap Text on, so if there is a lot of text, the row expands to handle it. If there isn't, though, I wind up with some comparatively skinny rows. I would like to set all my rows heights to at least 25.5; if, however, text in the row is making row height greater then 25.5, don't touch it. (Several answers to the first post gave me code to set every row to 25.5. Thank you, but that will set expanded too narrow to read all the text inside.) At the moment, the only way I can think of to accomplish this is by iterating through every row using the code below. That's a lot of time over several thousand rows. Is there a faster way to accomplish this using a range? Ed For iRow = numRow To 2 Step -1 If Rows(iRow).RowHeight < 25.5 Then Rows(iRow).RowHeight = 25.5 End If Next iRow |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Any way to do this with a range?
You're welcome, Ed. Let me know if you uncover anything clever.
-- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... Well, thanks for trying, Dick. What I've been doing is after writing everything (14,000+ rows at last count), is doing AutoFit over the entire range; but since that makes some rows very small, I looped back through and reset the smaller ones to a greater height for easier reading. I'll play with your suggestion: while I'm on that row, Auto fit and, if less than, set bigger. It might take a bit more time on that iteration, but it might save more time on an overall loop-through. I appreciate the help. Have a good one. Ed "Dick Kusleika" wrote in message ... Ed That stinks. Unless you can set the row height as you're writing the values (i.e. you're already looping through the cells to write the values and you set the row height at the same time), I think you're stuck looping through them to set the row height. If you can do at the same time, you would save one loop, but still not a very elegant solution. Dick "Ed" wrote in message ... Thanks for the reply, Dick. The spreadsheet is an index of reports being processed. As each report is opened for processing, the code reads a number of text strings and writes these strings into a "template" Excel file. If I set the heights prior to writing the text, the text is truncated; both the height and width are set, and text overflowing those borders doesn't show. I wish there was a row height setting of "at least" (like there is with Word tables). BTW, I've got XL2k, if that makes a difference. Ed "Dick Kusleika" wrote in message ... Ed How is the data getting into Excel? If you set all the row heights to 25.5 before the data is entered, then only those rows that need it should expand. I didn't test that though. If you can't set the row height before the data, then I think you're stuck with iterating through them. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ed" wrote in message ... I create a spreadsheet using code. Several columns are formatted with Wrap Text on, so if there is a lot of text, the row expands to handle it. If there isn't, though, I wind up with some comparatively skinny rows. I would like to set all my rows heights to at least 25.5; if, however, text in the row is making row height greater then 25.5, don't touch it. (Several answers to the first post gave me code to set every row to 25.5. Thank you, but that will set expanded too narrow to read all the text inside.) At the moment, the only way I can think of to accomplish this is by iterating through every row using the code below. That's a lot of time over several thousand rows. Is there a faster way to accomplish this using a range? Ed For iRow = numRow To 2 Step -1 If Rows(iRow).RowHeight < 25.5 Then Rows(iRow).RowHeight = 25.5 End If Next iRow |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost! | Excel Discussion (Misc queries) | |||
Repost for Ron | Excel Discussion (Misc queries) | |||
Named Range REPOST | Excel Worksheet Functions | |||
repost: seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
repost: plz help- dynamic range with gaps? | Excel Discussion (Misc queries) |