Macro to TRIM all cell contents within a column

[Tanner_2004]

I was hoping to invoke a macro that will TRIM, any leading and trailing blanks, assuming they exist, and leave all TRIMMED values in their respective current cell.

We can assume the column is C, and the first cell is to be evaluated for TRIMMING is C2. The last cell in the column will be dynamic.

Thank you for reading and any suggestions!

 

[Eric W]

Sub TrimIt()
Dim r As Long


    For r = 2 To Columns(3).End(xlDown).Row
        Cells(r, 3) = Trim(Cells(r, 3))
    Next r
    
End Sub

 

[hiker95]

Tanner_2004,

Here is a macro solution for you to consider that does not do any looping thru the rows.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	C
1
2	aaa aaa
3	b
4	c
5	ddd ddd
6	e eee e
7
Sheet1

And, after the macro:

Excel 2007
	C
1
2	aaa aaa
3	b
4	c
5	ddd ddd
6	e eee e
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Tanner_2004()
' hiker95, 12/15/2015, ME908967
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  With .Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Tanner_2004 macro.

 

[Dazzawm]

Tanner_2004,

Here is a macro solution for you to consider that does not do any looping thru the rows.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007

	C
aaa aaa
b
c
ddd ddd
e eee e

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

And, after the macro:

Excel 2007

	C
aaa aaa
b
c
ddd ddd
e eee e

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Tanner_2004()
' hiker95, 12/15/2015, ME908967
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  With .Range(.Range("C2"), .Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Tanner_2004 macro.

Hiker what would need changing so the trim macro would run on the column or group of cells I select please?

 

[hiker95]

Hiker what would need changing so the trim macro would run on the column or group of cells I select please?

Dazzawm,

1. Can I see a sample of your raw data?

2. And, can we see what the results should look like?

 

[Dazzawm]

Dazzawm,

1. Can I see a sample of your raw data?

2. And, can we see what the results should look like?

I haven't any data to hand. There is no reason you can't use the same data as you posted. I just want to highlight the column or range of cells rather than have the column embedded in the code.

 

[hiker95]

Dazzawm,

Try the following macro code on YOUR sample data.

Sub Dazzawm_2019()
' hiker95, 04/05/2019, ME908967
Application.ScreenUpdating = False
With ActiveSheet
  With Selection
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
Application.ScreenUpdating = True
End Sub

 

[Dazzawm]

Apologies for the late response I have been on holiday. When I select a column and run the code I get an error and when I debug it points to:-

.Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")

I could give you an example of the data but it does it on any column with any type of data?

Runtime error 1004

The item with the specified name wasn't found.

 

[hiker95]

Apologies for the late response I have been on holiday. When I select a column and run the code I get an error and when I debug it points to:-

.Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")

I could give you an example of the data but it does it on any column with any type of data?

Runtime error 1004

The item with the specified name wasn't found.

Dazzawm,

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

Click on the below link to see How to display your sheet, and, how to install, download, and, use the MrExcel HTML Maker:

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html


Or, it is always easier to help and test possible solutions if we could work with your actual file.

Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com.

Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

If the workbook contains confidential information, you could replace it with generic data.