3

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.

Improve this question
7
  • 1
    I don't think the software allows you to cherry-pick which aspects of a given cell formatting to keep and which to dump when copy-pasting. As far as I know, it's either all our nothing.
    – ParaDice
    Commented Apr 19, 2017 at 10:31
  • You say paste special is not an option and blame keyboard shortcuts as the reason. However ctrl-alt-v is the keyboard shortcut for paste special, "u" in that context is values/number fornat only, what's the problem with that?
    – Tyson
    Commented Apr 19, 2017 at 10:44
  • Yes: Paste, Special, No Borders; using the icons. If you use keyboard shortcuts, there is one called "all but borders".
    – ejbytes
    Commented Apr 19, 2017 at 10:58
  • I forgot "all except borders", but that is ctl-alt-v then "x" then enter.
    – Tyson
    Commented Apr 19, 2017 at 11:07
  • Aren't all the Paste Special options available via the keyboard using the Context menu key?
    – I say Reinstate Monica
    Commented Apr 19, 2017 at 11:39

3 Answers 3

Reset to default
3

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

Improve this answer
8
  • Sounds viable, I will test this.
    – Overmind
    Commented Apr 20, 2017 at 7:10
  • Tested, have to save the file in macro-enable format but that's fine. The problem is that this only seems to work for copy-paste, not for cut-paste. Any reason why paste special is not working with cut ?
    – Overmind
    Commented Apr 20, 2017 at 7:44
  • You don't have to save it as macro-enabled file. The macro should save in "Personal Workbook", not the default drop down selection. This should be a macro now embedded into Excel once made.
    – ejbytes
    Commented Apr 20, 2017 at 7:51
  • @Overmind Above comment forgot to add your username. Also, cut isn't copy. Cut, is moving your content to another cell(s).
    – ejbytes
    Commented Apr 20, 2017 at 7:53
  • Excel does not seem to allow paste special when cutting. Is that by design ? Can it be bypassed ?
    – Overmind
    Commented Apr 20, 2017 at 7:54
1

Using the keystrokes

Alt,h,v,b will paste "all except boarders".

Not as fast as ctrl+v but its not bad once your used to it.

Another option would be to assign Paste and remove Borders to the quick action toolbar under options then you could use the keystrokes alt,1 where 1 is a number based on where the action is in your toolbar.

Improve this answer
1
  • As I said in the comment, AltHVB actually opens the menus and navigates them.
    – Overmind
    Commented Apr 20, 2017 at 7:09
0

You dismiss "Paste Special" as Not-an-option, but blame keyboard shortcuts as the reason. "Paste Special" has a keyboard shortcut, it's ctrl-alt-v.

The context menu for Paste Special context menu then has several options that may be of use such as "All except borders" and "Values and number formats".

Try ctrl-alt-v, x, enter or ctrl-alt-v, u, enter one of those should meet your needs and uses keyboard shortcuts only.

Improve this answer
5
  • I'll give this a test, thanks. Ctrl-Alt-V spawns a choice menu, not useful at all.
    – Overmind
    Commented Apr 19, 2017 at 12:12
  • 1
    "All except borders" would be very useful -the exact needed thing- if there would be a way to make it permanent default for any copy/cut-paste operation.
    – Overmind
    Commented Apr 19, 2017 at 12:17
  • @Overmind Well there is a way. Should I help Tyson the rest of the way? LOL
    – ejbytes
    Commented Apr 19, 2017 at 18:58
  • @ejbytes make an answer or add it here either way , that's the most I know
    – Tyson
    Commented Apr 19, 2017 at 18:59
  • @ejbytes, if there's a solution, tell us. Closest thing I found was to use Alt+HVB which is not good because it spawns menus instead of just doing a paste.
    – Overmind
    Commented Apr 20, 2017 at 6:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .