VBA SpecialCells Macro bug

Question

I'm currently using the following code to automatically align numbers, formulas and text in a table row centrally except for the first column in the selection which aligns left if it's text and centrally if it's not.

Annoyingly, however, if I only select one row, the macro starts running for every cell above and beneath the selection. Is there a way to fix this?

Also, currently once the macro has finished running it ends on the first column of the selection. Is there a way to make it end with the selection I started with (i.e. if I've selected cells A1:D1, once it's done running the currently selected cell will be A1 but I'd like it to still be highlighting A1:D1).

Apologies if anything is unclear

Sub Test_align_left()
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    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

This is an example table I start with:

I select the first two rows:

And it works perfectly, the first row contains a number in the first column so it aligns centrally, and the second row has text in the first column so it aligns to the left - so far so good:

But, if I run the macro on this row:

Suddenly all cells with text align to the left, regardless of whether I selected them or not:

1. stop selecting things in code - it is rarely necessary. 2. If you only selected one cell, you should process it differently. You can't stop how `Specialcells` works in that case. — Rory, Aug 16 '18 at 08:29
3. Don't ever use `On Error Resume Next` without error handing. This just hides error messages but doesn't fix any errors, they still occur you just cannot see them. It's exactly like closing your eyes, which doesn't fix any errors too. • 4. Can you provide a screenshot and a [mcve], I can't reproduce what you describe even when using a table. • 5. Apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to your code. — Pᴇʜ, Aug 16 '18 at 08:31
Hi Rory and PEH, thank you for your quick replies, I've simplified the code and added screenshots so that it hopefully is clearer. While I think I understand what you'd like me to do, my main issue is that I'm quite new to VBA and therefore don't know how to do it. Would it be possible for you to amend the code above directly? Thanks, Thomas — Thomas_3454262534332, Aug 16 '18 at 08:52
When you call `SpecialCells` on a range with more than one cell, it only considers cells inside that range. When you call `SpecialCells` on a single cell, it considers the entire sheet. If `Selection` is a single row, then `Selection.Columns(1)` is a single cell. — GSerg, Aug 16 '18 at 08:58
Oh right, I understand now, do you know if there's a workaround? Thanks Gserg — Thomas_3454262534332, Aug 16 '18 at 09:00
Check how many cells selection has and don't call specialcells if it's one? — GSerg, Aug 16 '18 at 09:02
Know exactly what you want me to do, I'm just crap at VBA and therefore don't know how to do it. Would it be an if statement with a countcells function? — Thomas_3454262534332, Aug 16 '18 at 09:08

Rory · Accepted Answer · 2018-08-16T09:33:19.200

1

Something like this to handle the special case of one row:

Sub Test_align_left()
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter

        With .Columns(1)
            If .Cells.Count > 1 Then

                On Error Resume Next
                .SpecialCells(xlCellTypeConstants, xlTextValues).HorizontalAlignment = xlLeft
                .SpecialCells(xlCellTypeFormulas, xlTextValues).HorizontalAlignment = xlLeft
            Else
                If Not IsNumeric(.Value) Then .HorizontalAlignment = xlLeft
            End If

        End With
    End With
End Sub

edited Aug 16 '18 at 09:33

answered Aug 16 '18 at 09:14

Rory

32,730
5
32
35

1

I recommend to add a `On Error Goto 0` after the second `.SpecialCells` to reactivate error reporting. Just in case the OP is going to add more code after the `End If`. Otherwise he will run into not seeing his errors at all. • And `.Count > 1` must be `.Rows.Count > 1` or `.Cells.Count > 1` otherwise this is always `1` because `.Columns(1)` is always one column and `.Count` will give the column count. – Pᴇʜ Aug 16 '18 at 09:19
I think it's very close, but now if I select the first two rows (as in picture 2), then both the "a" and the "1" in the first column align left (as opposed to just the "a" aligning left). Any ideas? – Thomas_3454262534332 Aug 16 '18 at 09:24
@Thomas_3454262534332 change `.Count > 1` into `.Cells.Count > 1` to fix this, as I mentioned in my comment above. – Pᴇʜ Aug 16 '18 at 09:26
Actually PEH's suggestion seems to have fixed it, let me just double check it's all working (yep just saw you reply sorry) – Thomas_3454262534332 Aug 16 '18 at 09:26
Ok looks to be all working perfectly now. Really appreciate both of you helping out, let me know if there's some way I can reward/recommend you! – Thomas_3454262534332 Aug 16 '18 at 09:32
My last edit, thanks to @Pᴇʜ should fix that. I missed the `.Cells` out. – Rory Aug 16 '18 at 09:34

VBA SpecialCells Macro bug

1 Answers1