How to Remove Decimal in Excel Column

[anmita]

Hello Team,

Have Used this approach to convert column data type

Sub macrochangecolumnformat()
’ Column A
Columns(“A:A”).Select
With Selection
.NumberFormat = “General”
.Value = .Value
End With

’ Column J
Columns(“J:J”).Select
With Selection
.NumberFormat = “General”
.Value = .Value
End With

But it still contains decimal value Is there a way we can remove decimal values for Column A and J

Example if in Column A Value is Present as

66.1

Then it can be converted as 66

So decimal values are removed from Column

Thanks Team

[Parvathy (PS Parvathy)]

Hi @anmita

Try this macro code:

Sub RoundAmounts()
    Dim rngA As Range
    Dim rngJ As Range
    Dim cell As Range

    ' Set the range for column A
    Set rngA = ThisWorkbook.Sheets("Sheet2").Range("A2:A" & ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)

    ' Set the range for column J
    Set rngJ = ThisWorkbook.Sheets("Sheet2").Range("J2:J" & ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, "J").End(xlUp).Row)

    ' Loop through each cell in column A
    For Each cell In rngA
        ' Round the value to remove the decimal part
        cell.Value = Round(cell.Value, 0)
    Next cell

    ' Loop through each cell in column J
    For Each cell In rngJ
        ' Round the value to remove the decimal part
        cell.Value = Round(cell.Value, 0)
    Next cell
End Sub

Change the sheet name according to yours

Hope it helps!

[supriya117 (Supriya Allada)]

Hi @anmita

Try this:

Sub macrochangecolumnformat()
    ' Column A
    Columns("A:A").Select
    With Selection
        .NumberFormat = "General"
        .Value = .Value
        For Each cell In Selection
            If IsNumeric(cell.Value) Then
                cell.Value = Int(cell.Value)
            End If
        Next cell
    End With

    ' Column J
    Columns("J:J").Select
    With Selection
        .NumberFormat = "General"
        .Value = .Value
        For Each cell In Selection
            If IsNumeric(cell.Value) Then
                cell.Value = Int(cell.Value)
            End If
        Next cell
    End With
End Sub

[rlgandu (Rajyalakshmi Gandu)]

@anmita

Sub macrochangecolumnformat()
    Dim lastRowA As Long, lastRowJ As Long
    Dim cell As Range
    Dim ws As Worksheet
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet1" to your sheet name
    
    ' Find the last row with data in column A
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Find the last row with data in column J
    lastRowJ = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
    
    ' Loop through each row with data in column A
    For Each cell In ws.Range("A1:J" & lastRowA)
        If IsNumeric(cell.Value) Then
            cell.Value = Round(cell.Value, 0)
        End If
    Next cell
    
    ' Loop through each row with data in column J
   
End Sub

[lrtetala (Lakshman Reddy)]

Hi @anmita

How about the following?

Input:

Output:

Hope it helps!!

[sreejith.ss]

Hey @anmita please try the below code
Main.xaml (12.5 KB)

In the final section please write the datatable to your required excel file

Regards
Sreejith S S

[Parvathy (PS Parvathy)]

Hi @anmita

→ Input

Workflow:

Macros code text file path:
Decimal.txt (794 Bytes)

Output:

Regards

[anmita]

Thanks @rlgandu , @Parvathy , @supriya117 , @lrtetala and @sreejith.ss for providing various solutions

[Parvathy (PS Parvathy)]

Hi @anmita

If you find solution for your query pleas mark my post as solution to close the loop.

Regards