How to remove hyperlinks without removing formatting?
In Excel, when we delete the hyperlinks which have some formatting, such as background color, font, size and so on, the formatting will be removed as well as following screenshot shown. But, sometimes, we need to keep the cell formatting when removing the hyperlinks. Are there any ways to help us to solve this annoyed problem?
 |
 |
 |
Remove hyperlinks without removing formatting with VBA code
Remove hyperlinks without removing formatting with VBA code
In Excel, there is no direct way to deal with this task, but you can apply the following VBA code to preserve the cell formatting when removing hyperlinks.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Remove hyperlinks without removing formatting
Sub RemoveHlinks()
'Update 20141024
Dim Rng As Range
Dim WorkRng As Range
Dim TempRng As Range
Dim UsedRng As Range
Dim xLink As Hyperlink
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set UsedRng = Application.ActiveSheet.UsedRange
For Each xLink In WorkRng.Hyperlinks
Set TempRng = Cells(1, UsedRng.Column + UsedRng.Columns.Count)
Set Rng = xLink.Range
Rng.Copy TempRng
Rng.ClearHyperlinks
Set TempRng = TempRng.Resize(Rng.Rows.Count, Rng.Columns.Count)
TempRng.Copy
Rng.PasteSpecial xlPasteFormats
TempRng.Clear
Next
End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to remind you selecting the cells which contains the hyperlinks, see screenshot:
4. And then click OK to close the dialog, the hyperlinks are removed, but the formatting of the hyperlinks (including the underlines) is remained.
Related articles:
How to change multiple hyperlink paths at once in Excel?
How to extract actual addresses from hyperlinks in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
