Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format even cells to the right.
Sub Formattoright()
Range("A2,A4,A6,A8,A10,A12,A14,A16,A18,A20,A22,A24 ,A26,A28,A30,A32,A34,A36,A38,A40,A42,A44,A46,A48,A 50,A52,A54,A56,A58,A60,A62,A64,A66,A68,A70,A72,A74 ,A76,A78,A80").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub This macro selects all even cells in column A and formats them so the contents move to the right side of the cell. Is there a way to speed this up so that the macro does not have to select each even cell individually? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format even cells to the right.
Sub OddLeftEvenRight()
Dim i As Long, s As String Dim last As Long last = 10000 ' << change to suit Range("A1:A" & last).HorizontalAlignment = xlLeft Range("A1:A" & last).Value = "L" ' for testing For i = 2 To last Step 2 s = s & "A" & i If Len(s) 230 Then ' keep well under 255 Range(s).HorizontalAlignment = xlRight Range(s).Value = "R" ' for testing s = "" ElseIf i < last Then s = s & "," End If Next If Len(s) Then Range(s).HorizontalAlignment = xlRight Range(s) = "R" ' for testing End If End Sub Regards, Peter T "Sal" wrote in message ... Sub Formattoright() Range("A2,A4,A6,A8,A10,A12,A14,A16,A18,A20,A22,A24 ,A26,A28,A30,A32,A34,A36,A38,A40,A42,A44,A46,A48,A 50,A52,A54,A56,A58,A60,A62,A64,A66,A68,A70,A72,A74 ,A76,A78,A80").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub This macro selects all even cells in column A and formats them so the contents move to the right side of the cell. Is there a way to speed this up so that the macro does not have to select each even cell individually? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format even cells to the right.
to cater for possibility of 'last' being an odd number need to change
ElseIf i < last Then to ElseIf i < last - 1 Then Peter T "Peter T" <peter_t@discussions wrote in message ... Sub OddLeftEvenRight() Dim i As Long, s As String Dim last As Long last = 10000 ' << change to suit Range("A1:A" & last).HorizontalAlignment = xlLeft Range("A1:A" & last).Value = "L" ' for testing For i = 2 To last Step 2 s = s & "A" & i If Len(s) 230 Then ' keep well under 255 Range(s).HorizontalAlignment = xlRight Range(s).Value = "R" ' for testing s = "" ElseIf i < last Then s = s & "," End If Next If Len(s) Then Range(s).HorizontalAlignment = xlRight Range(s) = "R" ' for testing End If End Sub Regards, Peter T "Sal" wrote in message ... Sub Formattoright() Range("A2,A4,A6,A8,A10,A12,A14,A16,A18,A20,A22,A24 ,A26,A28,A30,A32,A34,A36,A38,A40,A42,A44,A46,A48,A 50,A52,A54,A56,A58,A60,A62,A64,A66,A68,A70,A72,A74 ,A76,A78,A80").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub This macro selects all even cells in column A and formats them so the contents move to the right side of the cell. Is there a way to speed this up so that the macro does not have to select each even cell individually? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format even cells to the right.
By not selecting the cells it should speed up. Also if all you want to do is
right format the data, some of the steps can be commented out (or deleted): Sub Formattoright() for i1=2 to 80 step 2 With cells(i1,1) .HorizontalAlignment = xlRight ' .VerticalAlignment = xlBottom ' .WrapText = False ' .Orientation = 0 ' .AddIndent = False ' .IndentLevel = 0 ' .ShrinkToFit = False ' .ReadingOrder = xlContext ' .MergeCells = False End With next i1 End Sub "Sal" wrote: Sub Formattoright() Range("A2,A4,A6,A8,A10,A12,A14,A16,A18,A20,A22,A24 ,A26,A28,A30,A32,A34,A36,A38,A40,A42,A44,A46,A48,A 50,A52,A54,A56,A58,A60,A62,A64,A66,A68,A70,A72,A74 ,A76,A78,A80").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub This macro selects all even cells in column A and formats them so the contents move to the right side of the cell. Is there a way to speed this up so that the macro does not have to select each even cell individually? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format even cells to the right.
You could drop the Selection completely:
with range("a2,...,A80) .horizontalalignment = ... ... But if you meant so that you didn't have to type out the addresses in your code... Option Explicit Sub testme01() Dim myRng As Range Dim iRow As Long With ActiveSheet Set myRng = .Range("A2") For iRow = 4 To 80 Step 2 Set myRng = Union(myRng, .Cells(iRow, "A")) Next iRow End With With myRng .HorizontalAlignment = xlRight ....rest of that code End With End Sub Sal wrote: Sub Formattoright() Range("A2,A4,A6,A8,A10,A12,A14,A16,A18,A20,A22,A24 ,A26,A28,A30,A32,A34,A36,A38,A40,A42,A44,A46,A48,A 50,A52,A54,A56,A58,A60,A62,A64,A66,A68,A70,A72,A74 ,A76,A78,A80").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub This macro selects all even cells in column A and formats them so the contents move to the right side of the cell. Is there a way to speed this up so that the macro does not have to select each even cell individually? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format cells in Excel 2007 (i.e. currency format)? | Excel Discussion (Misc queries) | |||
How do I format cells to show date in Australian not US format? | Excel Worksheet Functions | |||
want format cells alignment not format cells font style | Excel Discussion (Misc queries) | |||
Cells won't convert to number format, even after format/cells/num. | Excel Discussion (Misc queries) | |||
When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions |