Excel – General Excel setting to ignore borders when copy-pasting

microsoft excelmicrosoft-excel-2007text formatting

I have a very big excel (office 2007) file that has borders of various colors between all cells.

There's a lot of cut/copy-pasting of various cells to other cells.

When cut/copy-pasting is done, the cell borders sometimes mix up. It is strange, because sometimes the borders remain intact, sometimes they are part of the copy-paste.

Is there a general setting so that when a cell cut/copy-paste is done only the content in the cell will be transmitted (with font and color) but not the cell borders ?

Paste special is not an option, the copy-pastes are done via keyboard shortcuts one.

Also, copy-pasting the content from the upper bar, when a cell is selected, will not keep the font and color of the text, it will convert it like the settings from the destination cell.

Best Answer

Is there a general setting so that when a cell copy-paste is done only the content in the cell will be transmitted (with font and color) but not the cell borders ?

Paste special is not an option, the copy-pastes are done via keyboard shortcuts one.

Following my comment:

Yes: Paste, Special, No Borders; using the icons. If you use keyboard shortcuts, there is one called "all but borders". – ejbytes 19 hours ago

To take it a step further, just add it to your Personal Macro list. Why not? That's why it's there. Here we go:

Do you have a Developer's Tab? Not there? Enable it. Correct this by: File → Options, Customize Ribbon, Popular Commands → Main Tabs section → Select Developer checkbox.

Select Developer's tab:

Code Section:

  • Click Record Macro.
  • Give is a meaninful name like "pastespecialnoborders".
  • Assign a meaningful Shortcut Key combo:Ctrl+Shift+V easy to remember right?
  • Store Macro In: Personal Macro Workbook (this makes is work Excel wide).
  • On current sheet: Copy a cell with borders on it → paste special → No Borders.
  • Click Stop Macro.
  • Press Alt + F11 → Modules → Module1 (double
    click it to get inside of it).
  • Go back to your Module, the code, and modify it so it only has this line:
    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Test your new Macro Shorcut Key. Copy a cell with borders on it. Use your keyboard shortcut combo. Done.

You can also add this Macro-shortcut to your Ribbon or add it to your Quick Access Toolbar.

enter image description here

enter image description here

Related Question