Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Hi all, i have an automated spread sheet that i need to change the
column widths to the following with code... A=6, B=7 & C=1... It must carry on with D=6, E=7 & F=1... doing the width of the data on the spreadsheet... Any help would be welcomed... Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Here you go, place in the sheets code, change 100 to what column you want to
stop at: Sub main() For i = 1 To 100 Step 3 Columns(i).ColumnWidth = 6 Columns(i + 1).ColumnWidth = 7 Columns(i + 2).ColumnWidth = 1 Next End Sub -- -John Please rate when your question is answered to help us and others know what is helpful. "Les Stout" wrote: Hi all, i have an automated spread sheet that i need to change the column widths to the following with code... A=6, B=7 & C=1... It must carry on with D=6, E=7 & F=1... doing the width of the data on the spreadsheet... Any help would be welcomed... Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Les,
The following should work. It will set the column widths for the used range across the sheet in sets of three. If the sheet is empty of data before beginning, change the "ActiveSheet.UsedRange.Columns.Count" to the max columns you want. Option Explicit Public Sub SetColWidths() Dim i As Integer For i = 1 To ActiveSheet.UsedRange.Columns.Count Step 3 Columns(i).ColumnWidth = 6 Columns(i + 1).ColumnWidth = 7 Columns(i + 2).ColumnWidth = 1 Next i End Sub "Les Stout" wrote in message ... Hi all, i have an automated spread sheet that i need to change the column widths to the following with code... A=6, B=7 & C=1... It must carry on with D=6, E=7 & F=1... doing the width of the data on the spreadsheet... Any help would be welcomed... Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
This is what you need:
Sub setmywidth() For i = 1 To 253 Cells(1, i).ColumnWidth = 6 Cells(1, i).Offset(0, 1).ColumnWidth = 7 Cells(1, i).Offset(0, 2).ColumnWidth = 1 i = i + 2 Next i End Sub Michael Arch. Please click on the was this posting helpful if it was "Les Stout" wrote: Hi all, i have an automated spread sheet that i need to change the column widths to the following with code... A=6, B=7 & C=1... It must carry on with D=6, E=7 & F=1... doing the width of the data on the spreadsheet... Any help would be welcomed... Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Ok, i have made the following, which is now working but my problem is
that the width(amount of Columns) can be variable, can one use this in conjunction with a variable ??? Sub MakeThemFitBetter() ' Dim vSizes As Variant, lCol As Long Dim N As Long, ws As Worksheet Set ws = ActiveSheet lCol = ActiveSheet.UsedRange.Columns.Count 'one number for each column width vSizes = Array(6, 7, 1, 6, 7, 1, 6, 7, 1)'<--Variable ?? For N = 1 To lCol ws.Columns(N).ColumnWidth = vSizes(N - 1) Next End Sub Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Thank you both so much, i have used the code you supplied... :0) working
100% thank you again Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Your method works fine but will throw an error if the number of columns is
ever more than 9 becasue vSizes = Array(6, 7, 1, 6, 7, 1, 6, 7, 1) puts only 9 numbers in your array if n is ever more than 9 it will crash. To work around what will be the resoning behind why a certain column is a certain size? -- -John Please rate when your question is answered to help us and others know what is helpful. "Les Stout" wrote: Ok, i have made the following, which is now working but my problem is that the width(amount of Columns) can be variable, can one use this in conjunction with a variable ??? Sub MakeThemFitBetter() ' Dim vSizes As Variant, lCol As Long Dim N As Long, ws As Worksheet Set ws = ActiveSheet lCol = ActiveSheet.UsedRange.Columns.Count 'one number for each column width vSizes = Array(6, 7, 1, 6, 7, 1, 6, 7, 1)'<--Variable ?? For N = 1 To lCol ws.Columns(N).ColumnWidth = vSizes(N - 1) Next End Sub Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Found that, that is why i went with your code, thanks John..... Much
appreciated. Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
here is a workaround for you to try:
Sub MakeThemFitBetter() Dim vSizes As Variant, lCol As Long Dim N As Long, ws As Worksheet Dim X As Long Set ws = ActiveSheet lCol = ActiveSheet.UsedRange.Columns.Count 'one number for each column width vSizes = Array(6, 7, 1) '<--Variable ?? For X = 1 To lCol ws.Columns(X).ColumnWidth = vSizes(N) N = N + 1 If N = 3 Then N = 0 Next End Sub -- Gary "Les Stout" wrote in message ... Found that, that is why i went with your code, thanks John..... Much appreciated. Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
Thanks Gary, i am ok with this now... Please could you help me with my
other thread.... Pulling my hair out !!! (Help to modify code) Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
looks like you solved it.
-- Gary "Les Stout" wrote in message ... Thanks Gary, i am ok with this now... Please could you help me with my other thread.... Pulling my hair out !!! (Help to modify code) Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change every 3 columns width with code
No Gary, it is a new thread subject" Help to modify code"
Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 How to change width of stacked columns | Charts and Charting in Excel | |||
How do I change the width of data columns in a column chart? | Charts and Charting in Excel | |||
Unwanted change in width of columns when showing formula. | New Users to Excel | |||
adding code to shrink columns to reasonable width | Excel Programming | |||
columns change width | Excel Discussion (Misc queries) |