CODEWITHSUNDEEP
vbaexcel
Thomas_3454262534332
Thomas_3454262534332

Reputation: 23

VBA Macro that works IF a cell contains text but not if it contains a number

Hi I'm currently using a macro that autoformats tables for me and aligns all cells centrally except for the ones in the first selected column.

I was wondering if there was a way to tweak this so that the 1st selected column is aligned left only if it contains text and not if it contains a number

Here's the code:

Sub Test_align_left()

With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

Selection.Columns(1).Select

On Error Resume Next

With Selection
    .SpecialCells(xlCellTypeConstants, xlTextValues).HorizontalAlignment = xlLeft
    .SpecialCells(xlCellTypeFormulas, xlTextValues).HorizontalAlignment = xlLeft
End With

End Sub

Thanks in advance,

Thomas

Upvotes: 0

Views: 379

Answers (2)

SJR
SJR

Reputation: 23081

If you mean left align if text or centred if numeric then here is a way which avoids looping through each cell.

Sub x()
On Error Resume Next
With Columns(1)
    .SpecialCells(xlCellTypeConstants, xlTextValues).HorizontalAlignment = xlLeft
    .SpecialCells(xlCellTypeConstants, xlNumbers).HorizontalAlignment = xlCenter
    .SpecialCells(xlCellTypeFormulas, xlNumbers).HorizontalAlignment = xlCenter
    .SpecialCells(xlCellTypeFormulas, xlTextValues).HorizontalAlignment = xlLeft
End With

End Sub

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166146

If you just want to leave the first column alone you could do something like:

Sub Test_align_left()

    'Test_align_left Macro

    With Selection.offset(0,1).resize(,Selection.columns.count-1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub

Upvotes: 1

Related Questions