format cells-vba

[avishain]

hello there,

got a table from A to K

in colomn A and J i got numbers formated as "Genearl".

the problem is that some of these numbers are formated as "Text" and in some cases there is a space at the begining of the number which are formated as text.

what i need is, a macro,that when i hit it will remove all spaces from the begining of the numbers (which are loctaed in colomn A and J) and i need that the whole colomns will be formated as General.


thanks

 

[Sektor]

In cell M1 type in and fill down:
=TRIM(A1)

In cell N1 type in and fill down:
=TRIM(J1)

After that copy as values results into A and J columns.

 

[Richard Schollar]

Hi

Try:

Sub Test()
With Application.Intersect(Activesheet.UsedRange,Range("A:A"))
  .NumberFormat = "General"
  .Replace " ",""
  .Formula = .Value
End With
With Application.Intersect(Activesheet.UsedRange,Range("J:J"))
  .NumberFormat = "General"
  .Replace " ",""
  .Formula = .Value
End With
End Sub

 

[JackDanIce]

Sorry misread, thought question was all columns between A and J inclusive

Try:

Sub Spud()
 
Dim i As Long, j As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
For i = 1 To 10
    For j = 1 To Cells(Rows.Count, i).End(xlUp).Row
        With Cells(j, i)
            .Value = Trim(Cells(j, i))
        End With
    Next j
    Columns(i).NumberFormat = "General"
Next
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[avishain]

thank u all,but it still isnt working.


nor trim neither replace have helped.....


its very strange cause when i count the number of digits with "Len" i got 3 digits eventhough there is a space at the begining followed by 3 digits,means i had to get "4" when counted the length ....right?

 

[JackDanIce]

Is there any indentation in the cell?

 

[avishain]

ok i found the problem.

i added to the code

.HorizontalAlignment = xlGeneral


thank u all!!1