if i have some values scattered that i want to line up to the right

Can this be achieved using formulas ,or VBA ?

Thanks

CodePudding user response：

Align Data

Before

After

Option Explicit

Sub AlignData()
        
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.UsedRange
        
        Dim rCount As Long: rCount = .Rows.Count
        Dim cCount As Long: cCount = .Columns.Count
        If rCount   cCount = 2 Then Exit Sub ' one cell only
        
        Dim Data As Variant: Data = .Value
        
        Dim cValue As Variant
        Dim r As Long
        Dim sc As Long
        Dim dc As Long
        
        For r = 1 To rCount
            dc = cCount
            For sc = cCount To 1 Step -1
                cValue = Data(r, sc)
                If Len(CStr(cValue)) > 0 Then
                    Data(r, sc) = Empty
                    Data(r, dc) = cValue
                    dc = dc - 1
                End If
            Next sc
        Next r
        
        .Value = Data
        
    End With
    
    MsgBox "Data aligned.", vbInformation
        
End Sub