VBA to right align text if the first characters are " "

[bbalch]

I have data in column A that I would like align right if =(LEFT($A10,4)=" ") is true.

Is there a way with VBA to run through all of the rows in column A and apply the align right to all the cells that are true?

Thanks for your help!

 

[Arul.rajesh]

You can use conditional Formatting to do that.

But, =(Left($A10,4)=" ") will be true IF and only IF =($A10=" ")

 

[Leith Ross]

Hello bbalch,

Since LEFT($A10, 4)=" " will never evaluate to true, there are 2 ways to interpret this. You either want to test if the data starts with 4 spaces or the 4th character is a space.

This VBA macro assumes the latter (the 4th character is space). It can be easily changed if you want to test for 4 leading spaces.

Sub AlignRight()

    Dim Cell    As Range
    Dim LastRow As Long
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1")
        Set LastRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row
        Set Rng = Rng.Resize(LastRow - Rng.Row + 1, 1)
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            
            For Each Cell In Rng
              ' to test for 4 leading spaces, change the line below to:   If Left(Cell, 4) = Space(4) Then
                If Mid(Cell, 4, 1) = " " Then 
                    Cell.HorizontalAlignment = xlHAlignRight
                End If
            Next Cell
            
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
            
End Sub

 

[bbalch]

Thanks Leith. I'm receiving a Compile Error: Object Required at this part of the code: Set LastRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row any suggestions?

Arul, how do you apply an alignment to a conditional format? I'm using Excel 2010 and that doesn't appear to be an option.

 

[Leith Ross]

Hello Hello bbalch,

Sorry, that was my fault. I originally had LastRow declared as a Range and later decided to declare it as a Long. Here is the corrected code...

Sub AlignRight()

    Dim Cell    As Range
    Dim LastRow As Long
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1")
        LastRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row
        Set Rng = Rng.Resize(LastRow - Rng.Row + 1, 1)
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            
            For Each Cell In Rng
              ' to test for 4 leading spaces, change the line below to:   If Left(Cell, 4) = Space(4) Then
                If Mid(Cell, 4, 1) = " " Then 
                    Cell.HorizontalAlignment = xlHAlignRight
                End If
            Next Cell
            
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
            
End Sub

 

[bbalch]

That did the trick. Thank you very much for your help Leith!

 

[Leith Ross]

Hello bbalch,

You're welcome.

 

[Rick Rothstein]

That did the trick. Thank you very much for your help Leith!

Not sure which option you used from Leith's code, but if your request is to right align all cell that start with 4 spaces, here is a fast (noticeable if you have a huge number of rows to process), non-looping macro that you can also consider...

Sub RightAlignFourLeadingSpaces()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.HorizontalAlignment = xlHAlignRight
  Columns("A").Replace "    *", "", xlWhole, SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub