vba help - number format multiple columns

[Mallesh23]

Hi Team
Below code help me in converting numbers in text to Number format.

I want to pass Column no in this line:=> ws.Range("A:A,C:C,F:F,I:I,L:L")).Areas , what will be code?..

Dim rA As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

For Each rA In Intersect(ws.UsedRange, ws.Range("A:A,C:C,F:F,I:I,L:L")).Areas
  With rA
    .NumberFormat = "General"
    .Value = .Value
Next rA
  End With

Thanks
mg

 

[Trebor76]

Hi Mallesh23,

Try this:

Option Explicit
Sub Macro1()

    Dim rA As Range
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    For Each rA In Intersect(ws.UsedRange, ws.Range("A:A,C:C,F:F,I:I,L:L")).Areas
        With rA
            .NumberFormat = "General"
            .Value = .Value
        End With
    Next rA
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Mallesh23]

Hi Robert,

Thanks for your help, I want to pass Column no.

I want to replace this line :=> ws.Range("A:A,C:C,F:F,I:I,L:L")).Areas

Something like :=> ws.Columns(1,3,6,12)).Areas


Thanks
mg

 

[Trebor76]

Maybe this (non-looping):

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
  
    Application.ScreenUpdating = False
  
    Set ws = ThisWorkbook.Worksheets("Sheet1")
  
    With Union(ws.Columns(1), ws.Columns(3), ws.Columns(6), ws.Columns(12))
        .NumberFormat = "General"
        .Value = .Value
    End With
  
    Application.ScreenUpdating = True

End Sub

Or based on your original code:

Option Explicit
Sub Macro2()

    Dim rA As Range
    Dim ws As Worksheet
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Worksheets("Sheet1")
   
    For Each rA In Intersect(ws.UsedRange, Union(ws.Columns(1), ws.Columns(3), ws.Columns(6), ws.Columns(12))).Areas
        With rA
            .NumberFormat = "General"
            .Value = .Value
        End With
    Next rA
   
    Application.ScreenUpdating = True

End Sub

 

[Mallesh23]

Hi Trebor,

Thanks for your help, it worked, I modified small things as per my requirements.
One more help required , Creating a function which will convert text to number for single column and multiple column.
as per Parameter passed.

Below is my attempted code. It converts Multiple Columns how to pass single column.

Sub test()

Dim Multi_Column As Variant
Multi_Column = Array(1, 3, 5)

Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets("Sheet1")

formatText ws, Multi_Column

End Sub

Sub formatText(ByVal ws As Worksheet, Optional ByVal Multi_Column As Variant)

    Dim rA As Range
    Dim i As Long
   
    For i = LBound(Multi_Column) To UBound(Multi_Column)
        For Each rA In Intersect(ws.UsedRange, ws.Columns(Multi_Column(i))).Areas
            With rA
                .NumberFormat = "General"
                .Value = .Value
            End With
        Next rA
   Next i
    Application.ScreenUpdating = True

MsgBox "Macro successful"


End Sub

 

[Trebor76]

how to pass single column

Your array can take multiple columns (as you've done) or a single column like so:

Multi_Column = Array(1)

 

[Mallesh23]

Hi Trebor76,

Perfect ! Thanks for your help, it worked ! ?


Thanks
mg

 

[Trebor76]

You're welcome