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

Question

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

Take a look at the `isNumeric()` function https://www.techonthenet.com/excel/formulas/isnumeric.php — Marcucciboy2, Aug 15 '18 at 16:12
I've tried making an IF loop that includes IsNumeric and have gotten nowhere, hence the call for help. Could you outline how to include it in my current code? — Thomas_3454262534332, Aug 15 '18 at 16:17
It’s probably not necessary given the details you provided, but if you also wanted to be sure that there is text in either cell, you can use the `Len()` function https://www.techonthenet.com/excel/formulas/len.php — Marcucciboy2, Aug 15 '18 at 16:18
Yeah, you would say `If Not isNumeric(Selection.Value) Then With...` — Marcucciboy2, Aug 15 '18 at 16:19
It still seems to be aligning numbers to the left unfortunately, but thanks for the help — Thomas_3454262534332, Aug 15 '18 at 16:21
@Thomas_3454262534332, If you're having issues with selecting ranges, I'd suggest that you either place a watch on it (actually the address property), or print it to the Immediate window using `Debug.Print Selection.Columns(1).Address`. That way you can step through the code and fiddle with the range; get it right before you make any changes. — Profex, Aug 15 '18 at 17:43

SJR · Answer 1 · 2018-08-15T16:28:39.027

2

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

edited Aug 15 '18 at 16:28

answered Aug 15 '18 at 16:15

SJR

22,986
6
18
26

Thanks for the speedy reply, however Ideally I would like everything (formulas included) to align centrally and only text in the first column to align left – Thomas_3454262534332 Aug 15 '18 at 16:19
Well you can incorporate formulae into this (edited above). The code for other columns remains as is. If you've already centred everything you don't need the two xlcenter lines in this code. – SJR Aug 15 '18 at 16:22
It's giving me the error: "No cells were found", any ideas? – Thomas_3454262534332 Aug 15 '18 at 16:27
OK so we need to "ignore" the error if no cells are found in any of those categories - see the first line I have added. – SJR Aug 15 '18 at 16:30
Actually just noticed something strange, if I only select 1 row, then it aligns all cells that contain text in the row to the left (not just the first column) and does the same to the row beneath (i.e. a row i didn't even select). Sorry to have to bother you again but have you encountered this before? (Also how do I make it so that once the macro has run, I'm returned to my original highlighted section - i.e. if I select a range and run the macro, ideally that range would still be selected when its done)? – Thomas_3454262534332 Aug 15 '18 at 16:53
I guess you are not using code as posted but have updated? There is a bug with specialcells if only one cell is selected it considers the whole sheet similar cells. – SJR Aug 15 '18 at 17:05
Currently using: 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 – Thomas_3454262534332 Aug 15 '18 at 17:14
1

@Thomas_3454262534332, The first time, you aren't limiting the changes to the first column. Also, you only need the to change the `HorizontalAlignment` property; the rest is just noise. Read up on [how to avoid using select in excel vba](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Profex Aug 15 '18 at 17:35
Can you add that code to your original question? I’ll take a look later but Profex’s point is a good one. – SJR Aug 15 '18 at 17:58
Just added it now, sorry. I'm pretty new at VBA so while I am able to roughly follow what you guys are saying, I'm struggling with the execution haha – Thomas_3454262534332 Aug 16 '18 at 08:21
Why did you start a new question? – SJR Aug 16 '18 at 10:39

score 1 · Answer 2 · answered Aug 15 '18 at 16:16

1

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

answered Aug 15 '18 at 16:16

Tim Williams

154,628
8
97
125

Don't really want to ignore the first column, just want it to align left if it contains text, and align centrally if it contains anything else (even if blank) – Thomas_3454262534332 Aug 15 '18 at 16:23

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

2 Answers2