Created
February 26, 2018 04:13
-
-
Save DuongAQ/172466f5b2c0541062d9d69558c7514d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub RemoveLink_02() 'Excel VBA remove validation links from workbook. | |
Dim r As Range | |
Dim str As String | |
Dim sh As Worksheet | |
Dim ws As Worksheet | |
Set ws = ActiveSheet | |
Set sh = Sheets.Add | |
sh.Name = "ExternalLK" | |
For Each r In ws.UsedRange.Cells | |
On Error Resume Next 'Gặp lỗi thì bỏ qua | |
str = "" 'Đưa biến về giá trị rỗng để về dạng mặc định | |
str = r.Validation.Formula1 'Xét chuỗi trong các công thức | |
On Error GoTo 0 'Đưa cơ chế bẫy lỗi về mặc định | |
If InStr(1, str, "]") > 0 Then 'Xét toàn bộ các dòng trong bảng và bỏ qua chuỗi nào có gắn với dấu ngoặc vuông | |
sh.Range("A65536").End(xlUp)(2) = r.Address & " " & r.Validation.Formula1 | |
End If | |
Next r | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment