Format even cells to the right.

[Sal]

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,A50,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?

 

[Peter T]

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

 

[Peter T]

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

 

[Gleam]

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

 

[Dave Peterson]

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