CODEWITHSUNDEEP
vbaexcel
Thomas_3454262534332
Thomas_3454262534332

Reputation: 23

VBA SpecialCells Macro bug

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:

Pic1 - This is an example table I start with

I select the first two rows:

Pic2 - 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:

Pic3 - 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:

Pic4 - 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:

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

Upvotes: 1

Views: 240

Answers (1)

Rory
Rory

Reputation: 34045

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

Upvotes: 1

Related Questions