CODEWITHSUNDEEP
vbaexcel
Archangel
Archangel

Reputation: 11

VBA code to clear FORMATTING and leave plain text data in a chosen column in Excel

I need to create a button to add to Excel (2010 currently) that will strip all the formatting in that column and just leave plain text. Our current solution is to copy the data into Notepad to strip out the formatting and then copy it back in from Notepad. This works, but is inelegant and would be far easier if I could just create a button to do this within Excel itself. I've seen a few solutions posted but none of them seem to deal with a randomly selected column. Help!

Upvotes: 1

Views: 6418

Answers (3)

Wouter
Wouter

Reputation: 612

Just to add to Jeeped's answer:

Using this code you'll clear the whole columns formatting (select a random cell, run this code and the whole columns formatting has been cleared)

Sub ClearColumn()
i = ActiveCell.Column
    ActiveSheet.Columns(i).ClearFormats
End Sub

Upvotes: 1

user4039065
user4039065

Reputation:

The Range.ClearFormats method seems appropriate here.

With Worksheets("Sheet1")
    .Columns(1).ClearFormats  'clear formatting from column A
End With

'for a manually selected group of cells
Selection.ClearFormats  'clear formatting from the cells currently selected

fwiw, the Clear Formats command is available on the ribbon through Home ► Editing ► Clear ► Clear Formats (Alt+H+E+F). You could easily add that command to the QAT rather than create a macro that largely duplicates the command and assign it to a custom button.

Upvotes: 2

D_Bester
D_Bester

Reputation: 5911

Excel already has a button on the Home tab:

enter image description here

Just select the entire column and click Clear Formats

Upvotes: 1

Related Questions