Trim and clean up cell within range

Question

Hi all,

I am searching for a vba code to trim the cell values in the columns starting from column number 2 to 3 and 5 to 17, i.e.,  cell value from "B26" to the last value in column "Q".  However, do not Trim the values in column number 4 ("Column D") as it has values preceded with zeros, trimming that column removing the zeros. Also is it possible to "Round off" and clean up the cells in the same range.

Answer 1

Hi all,

Anyone can please check and help on this request

Answer 2

Hi Shakiru

Sorry for the delay in reply, I got the error in the line 
"cell. Value = Application.WorksheetFunction.Round(cell. Value, 2) 'Round off the value to 2 decimal places"

and when removing the line and trying running, the code keeps on running and does not end. I need to force close the code in Excel.

Answer 3

Hi Nagul1!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

Yes, please try the VBA code to trim cell values

Sub TrimAndRoundOff()

Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") 'Replace "Sheet1" with the name of your worksheet

Dim lastRow As Long lastRow = ws. Cells(ws. Rows.Count, "B"). End(xlUp). Row 'Find the last row in column B

Dim rng As Range Set rng = ws. Range("B26:C" & lastRow & ",E:Q") 'Define the range to trim and round off

Dim cell As Range For Each cell In rng If cell. Column <> 4 Then 'Check if the cell is not in column D cell. Value = Application.WorksheetFunction.Round(cell. Value, 2) 'Round off the value to 2 decimal places cell. Value = Trim(cell. Value) 'Trim the value End If Next cell

End Sub

Make sure to replace "Sheet1" with the name of your worksheet in the line Set ws = ThisWorkbook.Worksheets("Sheet1"). You can also adjust the number of decimal places to round off by changing the number 2 in the line cell. Value = Application.WorksheetFunction.Round(cell. Value, 2).

Best Regards, Shakiru