Tìm kiếm và liệt kê các name đang sử dụng trong file

viehoai · 19/7/11

Trong 1 file có rất nhiều name. Nhưng chỉ có một số name đang được sử dụng trong các công thức trong file
Vậy làm thế nào để liệt kê ra được các name đó ra bảng tính?
Xin cảm ơn các anh chị.

ndu96081631 · 19/7/11

viehoai đã viết:
Trong 1 file có rất nhiều name. Nhưng chỉ có một số name đang được sử dụng trong các công thức trong file
Vậy làm thế nào để liệt kê ra được các name đó ra bảng tính?
Xin cảm ơn các anh chị.

Dùng Find Method đi ---> For... Next qua các name, Dùng Find Method tìm name trong Formula
Thử suy nghĩ xem ---> Cũng dễ mà
Nếu chưa làm được hãy đưa file ví dụ lên đây nhé

bebo021999 · 19/7/11

Anh thử theo hướng dẫn sau đây , không biết phải ý anh không?

When you modify a workbook, a reference list of range names might be useful. A list is especially helpful if you didn’t create the workbook; it’s difficult to keep up with all the ranges you and others have created. To print a list of range names, do the following:

Select an out of the way spot in your workbook; you’re going to create the list in the workbook before printing it.
Select Name from the Insert menu and then choose Paste from the resulting submenu. (If there are no ranges in your workbook, this command isn’t available.)
When Excel displays the Paste Name dialog box, click Paste List.

Excel will generate a list of range names and their references. At this point, simply print the list and share it. Excel won’t automatically update the list, so you must remember to generate a new list when you add or delete ranges.

viehoai · 19/7/11

bebo021999 đã viết:
Anh thử theo hướng dẫn sau đây , không biết phải ý anh không?

When you modify a workbook, a reference list of range names might be useful. A list is especially helpful if you didn’t create the workbook; it’s difficult to keep up with all the ranges you and others have created. To print a list of range names, do the following:

Select an out of the way spot in your workbook; you’re going to create the list in the workbook before printing it.

Select Name from the Insert menu and then choose Paste from the resulting submenu. (If there are no ranges in your workbook, this command isn’t available.)

When Excel displays the Paste Name dialog box, click Paste List.

Excel will generate a list of range names and their references. At this point, simply print the list and share it. Excel won’t automatically update the list, so you must remember to generate a new list when you add or delete ranges.

Cảm ơn bebo, nhưng ý mình không phải vậy. Mình muốn liệt kê ra các Name đang sử dụng trong các công thức, chứ không phải là tất cả hay một số name cần lựa chọn. Mình sẽ thử làm theo hướng gợi ý của anh ndu xem sao.

huuthang_bd · 19/7/11

ndu96081631 đã viết:
Dùng Find Method đi ---> For... Next qua các name, Dùng Find Method tìm name trong Formula
Thử suy nghĩ xem ---> Cũng dễ mà
Nếu chưa làm được hãy đưa file ví dụ lên đây nhé

Về cơ bản là như vậy nhưng để thực hiện một cách tổng quát thì cũng khó lắm. Ví dụ như Name trùng với một đoạn Text trong công thức, Name trùng với một phần của tên hàm, hoặc Name không được sử dụng trong Sheet nhưng được sử dụng trong Name khác.

viehoai · 19/7/11

huuthang_bd đã viết:
Về cơ bản là như vậy nhưng để thực hiện một cách tổng quát thì cũng khó lắm. Ví dụ như Name trùng với một đoạn Text trong công thức, Name trùng với một phần của tên hàm, hoặc Name không được sử dụng trong Sheet nhưng được sử dụng trong Name khác.

Xin anh nói thêm hướng giải quyết
Cảm ơn anh

ndu96081631 · 19/7/11

viehoai đã viết:
Xin anh nói thêm hướng giải quyết
Cảm ơn anh

Thì bạn cứ làm bình thường đi. Trong quá trình làm tự nhiên ta sẽ rút ra kết luận
Mà hỏi thêm: Không biết bạn muốn liệt kê mấy name này nhằm mục đích gì?

viehoai · 19/7/11

ndu96081631 đã viết:
Thì bạn cứ làm bình thường đi. Trong quá trình làm tự nhiên ta sẽ rút ra kết luận
Mà hỏi thêm: Không biết bạn muốn liệt kê mấy name này nhằm mục đích gì?

Thật ra em làm file để xóa các name cho các anh em trong cơ quan sử dụng. Em làm thì không cần thiết như thế nhưng cho những người khác sử dụng họ chỉ biết kích chuột. Đa số các file có name rất khủng (có file đến gần 10.000names) dung lượng quá lớn (>5Mb). Sau khi em xóa name xong chưa đầy 1Mb. Nên em hỏi những cái cần thiết để làm file hoàn thiện cho họ

ndu96081631 · 19/7/11

viehoai đã viết:
Thật ra em làm file để xóa các name cho các anh em trong cơ quan sử dụng. Em làm thì không cần thiết như thế nhưng cho những người khác sử dụng họ chỉ biết kích chuột. Đa số các file có name rất khủng (có file đến gần 10.000names) dung lượng quá lớn (>5Mb). Sau khi em xóa name xong chưa đầy 1Mb. Nên em hỏi những cái cần thiết để làm file hoàn thiện cho họ

Thế thì thôi ta download chương trình dọn name rác của Nguyễn Duy Tuân về mà xài có phải khỏe không?

viehoai · 19/7/11

ndu96081631 đã viết:
Thế thì thôi ta download chương trình dọn name rác của Nguyễn Duy Tuân về mà xài có phải khỏe không?

Kính nhờ MOD xóa bài này. Xin cảm ơn

viehoai · 19/7/11

ndu96081631 đã viết:
Thế thì thôi ta download chương trình dọn name rác của Nguyễn Duy Tuân về mà xài có phải khỏe không?

1. Em làm để nâng cao kiến thức
2. Của Bác Tuân thì em cũng đang dùng, Nhưng có những name không phải là rác nhưng chẳng có tích sự gì thì cũng cho nó "về vườn" luôn

huuthang_bd · 19/7/11

viehoai đã viết:
Xin anh nói thêm hướng giải quyết
Cảm ơn anh

Bạn test thử file:

PHP:

Sub GPE()
Dim N As Name, Sh As Worksheet, Rng As Range, NArr(1 To 65536, 1 To 1), i As Long, j As Long, k As Long, RngAdd As String, Pos As Long, Dic As Variant
Set Dic = CreateObject("Scripting.Dictionary")
For Each N In Names
    For Each Sh In Sheets
        RngAdd = ""
        Set Rng = Sh.Cells.Find(N.Name, Sh.[A1], xlFormulas, 2)
        If Not Rng Is Nothing Then
            RngAdd = Rng.Address
            Do
                Set Rng = Sh.Cells.FindNext(Rng)
                    If Rng.HasFormula Then
                        k = CheckN(Rng.Formula, N.Name)
                        If k = 1 Then GoTo Fnext
                        If k = 2 Then
                            i = i + 1
                            NArr(i, 1) = N.Name
                            Dic.Add N.Name, ""
                            GoTo NextN
                        End If
                    End If
Fnext:
            Loop Until Rng.Address = RngAdd
        End If
    Next
NextN:
Next
Dim Check As Boolean
Do
Check = False
For Each N In Names
    If Not Dic.Exists(N.Name) Then
        k = i
        For j = 1 To k
            If CheckN(ActiveWorkbook.Names(NArr(j, 1)).RefersTo, N.Name) = 2 Then
                i = i + 1
                NArr(i, 1) = N.Name
                Dic.Add N.Name, ""
                Check = True
                GoTo CheckNext
            End If
        Next
    End If
CheckNext:
Next
Loop While Check
Sheets(1).[A2:A65536].ClearContents
If i > 0 Then Sheets(1).[A2].Resize(i).Value = NArr
End Sub

PHP:

Private Function CheckN(ByVal FStr As String, ByVal N As String) As Long
Dim Obj As Variant, Pos As Long
With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Global = True
    .Pattern = "[^a-z0-9.""]" & N & "[^a-z0-9.""]"
    FStr = .Replace(FStr & "+", vbBack)
    If InStr(FStr, vbBack) = 0 Then
        CheckN = 1
        Exit Function
    End If
    Do
        Pos = InStr(FStr, vbBack)
        If (Len(Left(FStr, Pos - 1)) - Len(Replace(Left(FStr, Pos - 1), """", ""))) Mod 2 = 0 Then
            CheckN = 2
            Exit Function
        End If
        FStr = """" & Right(FStr, Len(FStr) - Pos)
    Loop Until InStr(FStr, vbBack) = 0
End With
End Function

viehoai · 22/7/11

huuthang_bd đã viết:

Bạn test thử file:

PHP:

Sub GPE()
Dim N As Name, Sh As Worksheet, Rng As Range, NArr(1 To 65536, 1 To 1), i As Long, j As Long, k As Long, RngAdd As String, Pos As Long, Dic As Variant
Set Dic = CreateObject("Scripting.Dictionary")
For Each N In Names
    For Each Sh In Sheets
        RngAdd = ""
        Set Rng = Sh.Cells.Find(N.Name, Sh.[A1], xlFormulas, 2)
        If Not Rng Is Nothing Then
            RngAdd = Rng.Address
            Do
                Set Rng = Sh.Cells.FindNext(Rng)
                    If Rng.HasFormula Then
                        k = CheckN(Rng.Formula, N.Name)
                        If k = 1 Then GoTo Fnext
                        If k = 2 Then
                            i = i + 1
                            NArr(i, 1) = N.Name
                            Dic.Add N.Name, ""
                            GoTo NextN
                        End If
                    End If
Fnext:
            Loop Until Rng.Address = RngAdd
        End If
    Next
NextN:
Next
Dim Check As Boolean
Do
Check = False
For Each N In Names
    If Not Dic.Exists(N.Name) Then
        k = i
        For j = 1 To k
            If CheckN(ActiveWorkbook.Names(NArr(j, 1)).RefersTo, N.Name) = 2 Then
                i = i + 1
                NArr(i, 1) = N.Name
                Dic.Add N.Name, ""
                Check = True
                GoTo CheckNext
            End If
        Next
    End If
CheckNext:
Next
Loop While Check
Sheets(1).[A2:A65536].ClearContents
If i > 0 Then Sheets(1).[A2].Resize(i).Value = NArr
End Sub

PHP:

Private Function CheckN(ByVal FStr As String, ByVal N As String) As Long
Dim Obj As Variant, Pos As Long
With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Global = True
    .Pattern = "[^a-z0-9.""]" & N & "[^a-z0-9.""]"
    FStr = .Replace(FStr & "+", vbBack)
    If InStr(FStr, vbBack) = 0 Then
        CheckN = 1
        Exit Function
    End If
    Do
        Pos = InStr(FStr, vbBack)
        If (Len(Left(FStr, Pos - 1)) - Len(Replace(Left(FStr, Pos - 1), """", ""))) Mod 2 = 0 Then
            CheckN = 2
            Exit Function
        End If
        FStr = """" & Right(FStr, Len(FStr) - Pos)
    Loop Until InStr(FStr, vbBack) = 0
End With
End Function

Kính nhờ anh Thắng xem giúp em khi áp dụng vào file em báo lỗi, làm mãi chưa được
Anh giúp em thêm cải thiện tốc độ thêm tí nhé
Xin cảm ơn anh

ndu96081631 · 22/7/11

viehoai đã viết:
Kính nhờ anh Thắng xem giúp em khi áp dụng vào file em báo lỗi, làm mãi chưa được
Anh giúp em thêm cải thiện tốc độ thêm tí nhé
Xin cảm ơn anh

File của bạn đâu phải chỉ có name rác thôi đâu. Còn cả 1 đống sheet virus nữa ---> Thôi tự làm bằng tay đi cho chắc (vì chỉ xóa name thôi thì không ăn thua)
--------------
Ngoài ra file này còn có 1 "rừng" các object ---> Xóa hết chúng, dung lượng giảm từ 5MB còn có 600KB

viehoai · 22/7/11

ndu96081631 đã viết:
File của bạn đâu phải chỉ có name rác thôi đâu. Còn cả 1 đống sheet virus nữa ---> Thôi tự làm bằng tay đi cho chắc (vì chỉ xóa name thôi thì không ăn thua)

Dạ, em biết điều đó và đã có cách xử rồi. Xin giúp đỡ áp dụng code của Anh Thắng mà mắc phải lỗi. Nếu em đưa thêm câu lệnh

PHP:

On Error Resume Next

Thì nó sẽ liệt kê thêm một số Name không áp dụng

ndu96081631 · 22/7/11

ndu96081631 đã viết:
Ngoài ra file này còn có 1 "rừng" các object ---> Xóa hết chúng, dung lượng giảm từ 5MB còn có 600KB

Ah, thêm 1 phát hiện nữa, nếu Break Links luôn thì dung lượng chỉ còn 300KB
Ẹc... Ẹc...
(hổng biết file này bạn mở có thấy nặng không chứ trên máy tôi thì chỉ mở được nó bằng Excel 2007. Dùng Excel 2003 để mở file gốc, treo máy luôn)

viehoai · 22/7/11

ndu96081631 đã viết:
Ah, thêm 1 phát hiện nữa, nếu Break Links luôn thì dung lượng chỉ còn 300KB
Ẹc... Ẹc...
(hổng biết file này bạn mở có thấy nặng không chứ trên máy tôi thì chỉ mở được nó bằng Excel 2007. Dùng Excel 2003 để mở file gốc, treo máy luôn)

Dạ, đúng thế em diệt xong còn 364KB. Em sử dụng Excel 2003 mở ra thì rất nha, chạy bình thường mà Sư Phụ, thậm chí xuất các Name vẫn không phải là quá chậm (Không hiểu sao một số file khác thì chậm hơn nhiều)
----------
Sư Phụ sử dụng code để xóa Sheet "HelpMe" thử xem cho em với. Em làm thì tất cả các sheet khác xóa được riêng "thằng" này nó hổng chịu "đi"

Tìm kiếm và liệt kê các name đang sử dụng trong file

Thành viên gắn bó

Huyền thoại GPE

Thành viên gạo cội

Thành viên gắn bó

Chuyên gia GPE

Thành viên gắn bó

Huyền thoại GPE

Thành viên gắn bó

Huyền thoại GPE

Thành viên gắn bó

Thành viên gắn bó

Chuyên gia GPE

File đính kèm

Thành viên gắn bó

File đính kèm

Huyền thoại GPE

Thành viên gắn bó

Huyền thoại GPE

Thành viên gắn bó

Facebook

Group