PDA

View Full Version : Solved: Apply formula to all cells in a column



Klartigue
09-12-2011, 01:54 PM
Sub AlignText()
'
' Align Text

Range("AA5:AH5").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub

How do i get this to apply to all cells in these columns?

Thanks!

Bob Phillips
09-12-2011, 04:20 PM
Sub AlignText()
'
' Align Text

With Range("AA5:AH5").Resize(LastRow(ActiveSheet, ActiveSheet.Range("AA5")) - 4)
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

End Sub


Function LastRow(sh As Worksheet, StartAt As Range)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=StartAt, _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

justdriving
09-13-2011, 01:14 PM
XLD, This was too good.