copyorigin, but only from Left or right column, and not above or below.

[freddie mitchell]

Hello everyone and happy new year,

Quick question on VBA formatting.

I've got a code, pasted below, with an insert row function. The code works well, but one small issue is that the copyorigin section copies the format of cells above and below the inserted row. The cells above and below are, however, typically hyperlinked, meaning that the newly inserted row appears with a hyperlink. Is there a way to either set the format to a default setting? Or to copy the formatting of a specific cell that is formatted correctly?

.Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow

Thank you for all your help,

You're all great!

 

[jasonb75]

You would need to make any 'corrections' to the formatting after the row is inserted, something like

.Rows(r).Hyperlinks.Delete

if additional formatting changes are needed then either you can either apply them directly from your code or use pastespecial - formats.
If using pastespecial, try to limit it to the used columns only in order to reduce risk of potential file bloat.

 

[freddie mitchell]

Hi Jason,

thank you for this. To double check, does the

.Rows(r).Hyperlinks.Delete

need to have its own separate sub? I tried adding it to the insert row sub but it didn't work, probably because I put it in the wrong place!

Thank you for your help,

 

[jasonb75]

I would add it to the same sub as the next line after the row is inserted.

It might cause an error if there are no hyperlinks, if that is the case then try

    .Rows(r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
On Error Resume Next
    .Rows(r).Hyperlinks.Delete
On Error Goto 0