| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Resizing columns with a macro
Hi All
I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
I ran your code in Excel 2003 and it works for me (other than the oddity that
column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
I am using 2007 at work which is where I am at now and 2003 at home and have
the samme resluts at each??? very perplexing. I want to fix it but not sure what to do now?? anyone else have any ideas??? -- cruchnin numbers "JLatham" wrote: I ran your code in Excel 2003 and it works for me (other than the oddity that column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
I'm not sure what to tell you about your "not working" problem... like
JLatham, I get your code to work fine on my system also; with the same inability to set Column A to exactly 7.51 (this has to do with column width needing to be a whole number of pixels wide). While the following should not do anything to fix your problem, I did want to point out to you that there is no need for you to keep selecting ranges in order to work on them. Your eleven posted lines of code can be reduced to these three lines of code... Range("a:a").ColumnWidth = 7.51 Range("b:k,o:s").ColumnWidth = 17.57 Range("c:c,e:e,g:g,l:l,n:n").EntireColumn.Hidden = True -- Rick (MVP - Excel) "belvy123" wrote in message ... I am using 2007 at work which is where I am at now and 2003 at home and have the samme resluts at each??? very perplexing. I want to fix it but not sure what to do now?? anyone else have any ideas??? -- cruchnin numbers "JLatham" wrote: I ran your code in Excel 2003 and it works for me (other than the oddity that column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
Please double check your below statement. Try running the code again and
check the width of ColA. "But when I run the script it sizes all columns the same 17.57" The code works as it is in both version of XL; with col A with reduced width. When you set the width to 7.51 width (that means 7.51 characters long). The width actually adjusts to the next pixel. For example when you try to fix the width at 7.51 it adjust it to the next unit which is 7.57. This happens why because excel adjust the width to the nearest pixel . In pixels 54,55,56,57,58 etc; would get converted to 7.00, 7.14, 7.29, 7.43, 7.57 characters. Please check the below link to understand how the measurement works http://office.microsoft.com/en-us/ex...517241033.aspx Additionally, you dont need to select to change the column width. Columns("a").ColumnWidth = 7.51 Columns("b:k").ColumnWidth = 17.57 Columns("o:s").ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True If this post helps click Yes --------------- Jacob Skaria "belvy123" wrote: I am using 2007 at work which is where I am at now and 2003 at home and have the samme resluts at each??? very perplexing. I want to fix it but not sure what to do now?? anyone else have any ideas??? -- cruchnin numbers "JLatham" wrote: I ran your code in Excel 2003 and it works for me (other than the oddity that column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
HJ LATHAM
I input the condensed code you gave me and all is well it works fine now Crazy man I tell ya Thanks -- cruchnin numbers "Rick Rothstein" wrote: I'm not sure what to tell you about your "not working" problem... like JLatham, I get your code to work fine on my system also; with the same inability to set Column A to exactly 7.51 (this has to do with column width needing to be a whole number of pixels wide). While the following should not do anything to fix your problem, I did want to point out to you that there is no need for you to keep selecting ranges in order to work on them. Your eleven posted lines of code can be reduced to these three lines of code... Range("a:a").ColumnWidth = 7.51 Range("b:k,o:s").ColumnWidth = 17.57 Range("c:c,e:e,g:g,l:l,n:n").EntireColumn.Hidden = True -- Rick (MVP - Excel) "belvy123" wrote in message ... I am using 2007 at work which is where I am at now and 2003 at home and have the samme resluts at each??? very perplexing. I want to fix it but not sure what to do now?? anyone else have any ideas??? -- cruchnin numbers "JLatham" wrote: I ran your code in Excel 2003 and it works for me (other than the oddity that column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
Really? It works now? Hmm, I'm not sure what to tell you about that either;
but I am glad you have it working now (I just wish I understood why your other code didn't work for you when it worked for the rest of us... I hate mysteries like this). -- Rick (MVP - Excel) "belvy123" wrote in message ... HJ LATHAM I input the condensed code you gave me and all is well it works fine now Crazy man I tell ya Thanks -- cruchnin numbers "Rick Rothstein" wrote: I'm not sure what to tell you about your "not working" problem... like JLatham, I get your code to work fine on my system also; with the same inability to set Column A to exactly 7.51 (this has to do with column width needing to be a whole number of pixels wide). While the following should not do anything to fix your problem, I did want to point out to you that there is no need for you to keep selecting ranges in order to work on them. Your eleven posted lines of code can be reduced to these three lines of code... Range("a:a").ColumnWidth = 7.51 Range("b:k,o:s").ColumnWidth = 17.57 Range("c:c,e:e,g:g,l:l,n:n").EntireColumn.Hidden = True -- Rick (MVP - Excel) "belvy123" wrote in message ... I am using 2007 at work which is where I am at now and 2003 at home and have the samme resluts at each??? very perplexing. I want to fix it but not sure what to do now?? anyone else have any ideas??? -- cruchnin numbers "JLatham" wrote: I ran your code in Excel 2003 and it works for me (other than the oddity that column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Resizing columns with a macro
Thanks for that link - I was trying to find that (or any) explanation of how
the width setting worked, but it was just too late in the day for me to hunt enough. "Jacob Skaria" wrote: Please double check your below statement. Try running the code again and check the width of ColA. "But when I run the script it sizes all columns the same 17.57" The code works as it is in both version of XL; with col A with reduced width. When you set the width to 7.51 width (that means 7.51 characters long). The width actually adjusts to the next pixel. For example when you try to fix the width at 7.51 it adjust it to the next unit which is 7.57. This happens why because excel adjust the width to the nearest pixel . In pixels 54,55,56,57,58 etc; would get converted to 7.00, 7.14, 7.29, 7.43, 7.57 characters. Please check the below link to understand how the measurement works http://office.microsoft.com/en-us/ex...517241033.aspx Additionally, you dont need to select to change the column width. Columns("a").ColumnWidth = 7.51 Columns("b:k").ColumnWidth = 17.57 Columns("o:s").ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True If this post helps click Yes --------------- Jacob Skaria "belvy123" wrote: I am using 2007 at work which is where I am at now and 2003 at home and have the samme resluts at each??? very perplexing. I want to fix it but not sure what to do now?? anyone else have any ideas??? -- cruchnin numbers "JLatham" wrote: I ran your code in Excel 2003 and it works for me (other than the oddity that column A actually turns out to be 7.57 vs 7.51 wide). I found I couldn't set it to 7.51 manually either. "belvy123" wrote: Hi All I am using the following macro to resize columns as well as hide several columns however there is one column that i want to be a differnet size "7.51 width" as stated un the macro. But when I run the script it sizes all columns the same 17.57 how is it doing this and what do I need to do to rectify this. below is the macro thanks Columns("a").Select Selection.ColumnWidth = 7.51 Columns("b:k").Select Selection.ColumnWidth = 17.57 Columns("o:s").Select Selection.ColumnWidth = 17.57 Columns("c").Hidden = True Columns("e").Hidden = True Columns("g").Hidden = True Columns("l").Hidden = True Columns("n").Hidden = True End Sub -- Thanks crunchin numbers |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Resizing columns [same data source] | Charts and Charting in Excel | |||
| Resizing cells in a selection without resizing entire sheet | Excel Discussion (Misc queries) | |||
| resizing columns | Excel Discussion (Misc queries) | |||
| Macro for resizing of columns? | Excel Discussion (Misc queries) | |||
| Resizing columns | Excel Discussion (Misc queries) | |||
Linear Mode
