To add variable space in a cell

[dhavnil06]

Hi There,

Is it possible to add variable space (but the character's length is fixed at 31) using VBA? I will show an example below.

It should add the space (subtracting the initial characters) but shouldn't exceed 31 characters in a cell. The length of initial characters can vary (it's not fixed). Hope this clears the issue I am having.

 

[RustamMika]

Hi try this:

Sub mmm()
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Do While Len(Cells(i, 1).Value) < 31
            Cells(i, 1).Value = Cells(i, 1).Value & Chr(32)
        Loop
    Next
    MsgBox "task complete"
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Assuming you will never have cells with more than 31 characters to start with. Another option is

Sub dhavnil()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",@&rept("" "",31-len(@)))", "@", .Address))
   End With
End Sub

 

[dhavnil06]

Hi & welcome to MrExcel.
Assuming you will never have cells with more than 31 characters to start with. Another option is

Sub dhavnil()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",@&rept("" "",31-len(@)))", "@", .Address))
   End With
End Sub

Hi Fluff,

Thanks for the reply, but I'll have characters after 31 to 61 length.

 

[dhavnil06]

Hi try this:

Sub mmm()
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Do While Len(Cells(i, 1).Value) < 31
            Cells(i, 1).Value = Cells(i, 1).Value & Chr(32)
        Loop
    Next
    MsgBox "task complete"
End Sub

Hi Rustom,

This is working fine but it is taking so much time to run. Is there a way to minimize the run-time?

 

[bebo021999]

Try this:

Option Explicit
Sub ADDspace()
Dim lr&, i&, rng
lr = Cells(Rows.Count, "A").End(xlUp).Row
rng = Range("A1:A" & lr).Value
For i = 1 To UBound(rng)
    If Len(rng(i, 1)) < 31 Then rng(i, 1) = Left(rng(i, 1) & String(31, " "), 31)
Next
Range("A1:A" & lr).Value = rng
End Sub

BEFORE

AFTER:

 

[Peter_SSs]

Welcome to the MrExcel board!

Doing them all at once, even if longer than 31 ..

Sub dhavnil_v2()
   With Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(len(@)>31,@,left(@&rept("" "",31),31))", "@", .Address))
   End With
End Sub

Before:

After:

 

[RustamMika]

Hi Rustom,

This is working fine but it is taking so much time to run. Is there a way to minimize the run-time?

Fixed...

Sub mmm()
Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range(Cells(1, 1), Cells(lr, 1)).Value
    For i = 1 To UBound(arr)
          Do While Len(arr(i, 1)) < 31
                arr(i, 1) = arr(i, 1) & Chr(32)
          Loop
    Next
    [a1].Resize(UBound(arr), 1).Value = Application.Transpose(arr)
Application.ScreenUpdating = True
MsgBox "task complete"
End Sub

 

[Fluff]

@RustamMika you might want to test that code.