Code xoá "rác trắng" trong bảng tính ?

TrungChinhs · 6/6/09

Trong bảng tính có các ký tự không hiển thị trong cel như các ký tự rỗng, ký tự trắng, số 0 (khi tắt chức năng hiển thị); các ký tự này trong Special không được coi là Blanks nhưng trong Autofilter thì lại được coi là Blanks, Tôi tạm gọi các ký tự này là "rác trắng".

Có thể do cách lấy dữ liệu hoặc lý do nào đó mà trong bảng dữ liệu nguồn của tôi có rất nhiều loại "rác này" gây khó khăn cho việc lọc, xử lý dữ liệu vì không biết đâu là rỗng (""), là ký tự trắng (" ") và đâu là số 0, Tôi thường phải dùng vòng lặp để xoá nhưng với cách này máy chạy rất chậm
ví dụ:

PHP:

Sub XoaRac()
    For i = 5 To 10000
    For j = 5 To 50
        If Cells(i, j) = 0 Then Cells(i, j).ClearContents
    Next: Next
End Sub

Bạn nào có cách xoá nhanh hơn giúp mình với: chỉ cách làm trực tiếp trên Excel (cách làm nhanh hơn), có thể viết hộ code hoặc gợi ý giải pháp. Thanks!

sealand · 6/6/09

Mình đảm bảo code này nhanh hơn code của bạn rất nhiều áp dụng cho cả trang luôn. Nếu muốn hạn chế vùng cần xóa thì thay cells bằng vùng

Mã:

Sub xoa()
Application.DisplayAlerts = False
    Cells.Replace What:="0", Replacement:=""
Application.DisplayAlerts = True
End Sub

TrungChinhs · 7/6/09

Cảm ơn sealand ! từ code của bạn mình thêm 2 trường hợp nữa là OK, đúng là với cách này thì vấn đề tốc miễn bàn.

PHP:

Sub xoa()
Application.DisplayAlerts = False
With Cells
    .Replace What:="0", Replacement:=""
    .Replace What:="""", Replacement:=""
    .Replace What:=" ", Replacement:=""
End With
Application.DisplayAlerts = True
End Sub

ndu96081631 · 7/6/09

Trung Chinh đã viết:
Cảm ơn sealand ! từ code của bạn mình thêm 2 trường hợp nữa là OK, đúng là với cách này thì vấn đề tốc miễn bàn.

PHP:

Sub xoa() Application.DisplayAlerts = False With Cells .Replace What:="0", Replacement:="" .Replace What:="""", Replacement:="" .Replace What:=" ", Replacement:="" End With Application.DisplayAlerts = True End Sub

E rằng phải thêm 1 vài điều kiện "lọc" nữa... nếu không nó xóa tuốt những gì nó tìm được trong công thức thì sao hả anh?
Ý em muốn nói chỉ tìm và thay thế trong vùng dử liệu thô chứ không tìm toàn bộ (công thức phải chừa ra)
Ví dụ: trong công thức có tham chiếu C10:C20... sau khi chạy code nó thành C1:C2 (số 0 bị xóa mất
Ngoài ra trong vùng dử liệu thô cũng có Number, cũng có khoảng trắng (cần thiết) chẳng lẽ xóa sạch?
Theo em thì nên thay With Cells thành With Selection ---> Chọn vùng nào, xóa vùng nấy ---> Mà nếu làm thế thì chẳng khác nào thao thác dưới đây:
- Chọn từ dòng cuối cùng có dử liệu đến dòng 65536 rồi Edit\Clear\All
- Chọn từ cột cuối cùng có dử liệu đến cột IV rồi cũng Edit\Clear\All
Vậy có phải nếu viết thành code thao tác trên thì nó là: Selection.Clear hoặc Selection.ClearContents (nếu chỉ muốn xóa dử liệu) không?

phamduylong · 7/6/09

ndu96081631 đã viết:
E rằng phải thêm 1 vài điều kiện "lọc" nữa... nếu không nó xóa tuốt những gì nó tìm được trong công thức thì sao hả anh?
Ý em muốn nói chỉ tìm và thay thế trong vùng dử liệu thô chứ không tìm toàn bộ (công thức phải chừa ra)

Có thể thêm dòng này trước:

Mã:

Selection.SpecialCells(xlCellTypeConstants, 23).Select

Nó chọn tất cả các ô có dữ liệu (không phải công thức), sau đó mới xóa.

ndu96081631 · 7/6/09

phamduylong đã viết:
Có thể thêm dòng này trước:

Mã:

Selection.SpecialCells(xlCellTypeConstants, 23).Select

Nó chọn tất cả các ô có dữ liệu (không phải công thức), sau đó mới xóa.

Cũng không được đâu anh à!
Vì điều đầu tiên ta cần thống nhất với nhau, đó là: "Thế nào gọi là rác"
Số 0, khoảng trắng... nằm trong dử liệu sao có thể là rác được!
Vì thế cho dù có Selection.SpecialCells(xlCellTypeConstants, 23) đi nữa, sau khi chạy code xong, số 1000 cũng sẽ biến thành số 1 ---> Nguy hiểm đấy!

TrungChinhs · 7/6/09

ndu96081631 đã viết:
Cũng không được đâu anh à!
Vì điều đầu tiên ta cần thống nhất với nhau, đó là: "Thế nào gọi là rác"
Số 0, khoảng trắng... nằm trong dử liệu sao có thể là rác được!
Vì thế cho dù có Selection.SpecialCells(xlCellTypeConstants, 23) đi nữa, sau khi chạy code xong, số 1000 cũng sẽ biến thành số 1 ---> Nguy hiểm đấy!

Như tôi đã nói ở trên dữ liệu này nằm trong bảng nguồn ta chỉ cần dữ liệu kiểu value nên sẽ không còn công thức, những dữ liệu trắng không hiển thị nên không biết đường nào mà sử dụng và do không sử dụng được nên tôi mới gọi là "rác".

Đối với trường hợp của tôi là sau khi lấy được dữ liệu nguồn tôi muốn dùng SpecialCells(4) để xử lý dữ liệu nhưng có nhiều ô nó không chọn chỉ vì loại "rác" này vì vậy tôi muốn xoá sạch chúng.

Đúng như Ndu nhận xét dùng các cách trên để xoá đối với số 0 là không ổn vì nó không phân biệt được số 0 đứng độc lập và số 0 đứng trong dãy số nên nó xơi ráo. Hiện nay tôi đang bó tay, rất mong được sự giúp đỡ của các bạn. Thanks !

sealand · 7/6/09

Theo mình vấn đề này không hẳn quá khó khăn như vậy. Mình muốn nêu giải pháp thôi còn khi sử dụng áp dụng các điều kiện tìm kiếm nâng cao thì chắc là không có vấn đề gì.

ndu96081631 · 8/6/09

Trung Chinh đã viết:
Như tôi đã nói ở trên dữ liệu này nằm trong bảng nguồn ta chỉ cần dữ liệu kiểu value nên sẽ không còn công thức, những dữ liệu trắng không hiển thị nên không biết đường nào mà sử dụng và do không sử dụng được nên tôi mới gọi là "rác".

Đối với trường hợp của tôi là sau khi lấy được dữ liệu nguồn tôi muốn dùng SpecialCells(4) để xử lý dữ liệu nhưng có nhiều ô nó không chọn chỉ vì loại "rác" này vì vậy tôi muốn xoá sạch chúng.

Đúng như Ndu nhận xét dùng các cách trên để xoá đối với số 0 là không ổn vì nó không phân biệt được số 0 đứng độc lập và số 0 đứng trong dãy số nên nó xơi ráo. Hiện nay tôi đang bó tay, rất mong được sự giúp đỡ của các bạn. Thanks !

Vậy sao anh không dùng AutoFilter, quét qua các cột, cái nào Blanks thì Clear?
Với AutoFilter anh hoàn toàn có thể lọc được số 0 nằm 1 mình

TrungChinhs · 8/6/09

ndu96081631 đã viết:
Vậy sao anh không dùng AutoFilter, quét qua các cột, cái nào Blanks thì Clear?
Với AutoFilter anh hoàn toàn có thể lọc được số 0 nằm 1 mình

Vì bảng có nhiều cột, tôi cũng đã nghĩ đến việc dùng vòng lặp để AutoFilter từng cột để xoá nhưng tôi chưa biết viết code thế nào?

ndu96081631 · 8/6/09

Trung Chinh đã viết:
Vì bảng có nhiều cột, tôi cũng đã nghĩ đến việc dùng vòng lặp để AutoFilter từng cột để xoá nhưng tôi chưa biết viết code thế nào?

Lấy ví dụ dử liệu của anh nằm tại A1:G30, và anh muốn Clear tất cả các số 0 + khoảng trắng trong dử liệu, Ta sẽ có code:

PHP:

Sub Test()
  Dim i As Long
  With Range("A1").CurrentRegion  '<--- xac dinh vung du lieu
    For i = 1 To .Columns.Count
      .AutoFilter i, "=", xlOr, 0
      .Offset(1, i - 1).Resize(, 1).SpecialCells(12).ClearContents
      .AutoFilter
    Next
  End With
End Sub

- Quét từ cột 1 đến cột cuối
- AutoFilter từng cột với điều kiện Blanks hoặc = 0
- Chọn dử liệu đang (tại cột đang Filter) hiện rồi ClearContents
- Hủy chế độ AutoFilter rồi chuyển vòng lập sang cột tiếp theo
Chỉ thế thôi
------------
Vì chỉ cò thể Filter 1 lúc 2 điều kiện, nên nếu anh còn điều kiện nào khác, hãy AutoFilter lần nữa rồi lại xóa tiếp

rollover79 · 8/6/09

ndu96081631 đã viết:
Lấy ví dụ dử liệu của anh nằm tại A1:G30, và anh muốn Clear tất cả các số 0 + khoảng trắng trong dử liệu, Ta sẽ có code:

PHP:

Sub Test() Dim i As Long With Range("A1").CurrentRegion '<--- xac dinh vung du lieu For i = 1 To .Columns.Count .AutoFilter i, "=", xlOr, 0 .Offset(1, i - 1).Resize(, 1).SpecialCells(12).ClearContents .AutoFilter Next End With End Sub

- Quét từ cột 1 đến cột cuối
- AutoFilter từng cột với điều kiện Blanks hoặc = 0
- Chọn dử liệu đang (tại cột đang Filter) hiện rồi ClearContents
- Hủy chế độ AutoFilter rồi chuyển vòng lập sang cột tiếp theo
Chỉ thế thôi
------------
Vì chỉ cò thể Filter 1 lúc 2 điều kiện, nên nếu anh còn điều kiện nào khác, hãy AutoFilter lần nữa rồi lại xóa tiếp

Cách này cũng hay, nhưng nếu 1 công thức trả về giá trị là 0 mà coi nó là rác thì có vẻ không ổn lắm.

ndu96081631 · 8/6/09

rollover79 đã viết:
Cách này cũng hay, nhưng nếu 1 công thức trả về giá trị là 0 mà coi nó là rác thì có vẻ không ổn lắm.

Đúng vậy! Nhưng trường hợp này đã được đề cập từ đầu rồi bạn à: Đây là dử liệu thô, không có công thức!

sealand · 8/6/09

Mình thấy các bạn làm cho giải pháp phức tạp nhiều. Đối với số 0 mình thấy chỉ cần điều chỉnh 1 chút thôi là được mà, cụ thể code sau đâu có xóa bậy cái gì đâu ( Kể cả công thức =0 nó đâu có xóa):

Mã:

Sub xoa()
Application.DisplayAlerts = False
    Cells.Replace What:="0", Replacement:="",[B][COLOR=Red] LookAt:=xlWhole[/COLOR][/B]
Application.DisplayAlerts = True
End Sub

rollover79 · 8/6/09

sealand đã viết:
Mình thấy các bạn làm cho giải pháp phức tạp nhiều. Đối với số 0 mình thấy chỉ cần điều chỉnh 1 chút thôi là được mà, cụ thể code sau đâu có xóa bậy cái gì đâu ( Kể cả công thức =0 nó đâu có xóa):

Mã:

Sub xoa() Application.DisplayAlerts = False Cells.Replace What:="0", Replacement:="",[B][COLOR=Red] LookAt:=xlWhole[/COLOR][/B] Application.DisplayAlerts = True End Sub

Không phải tự dưng mà người ta muốn làm phức tạp vấn đề đi đâu. Vẫn biết là với số 0 thì dùng Replace thì rất nhanh và đơn giản rồi. Nhưng bạn thử áp dụng nó với trường hợp là các dấu cách "bẩn" thì sao. Bạn thử đi sẽ biết cái nào phức tạp hơn cái nào. Tôi thấy code của Ndu hay là ở chỗ đó.

khaivinh · 9/6/09

ndu96081631 đã viết:
Lấy ví dụ dử liệu của anh nằm tại A1:G30, và anh muốn Clear tất cả các số 0 + khoảng trắng trong dử liệu, Ta sẽ có code:

PHP:

Sub Test() Dim i As Long With Range("A1").CurrentRegion '<--- xac dinh vung du lieu For i = 1 To .Columns.Count .AutoFilter i, "=", xlOr, 0 .Offset(1, i - 1).Resize(, 1).SpecialCells(12).ClearContents .AutoFilter Next End With End Sub

- Quét từ cột 1 đến cột cuối
- AutoFilter từng cột với điều kiện Blanks hoặc = 0
- Chọn dử liệu đang (tại cột đang Filter) hiện rồi ClearContents
- Hủy chế độ AutoFilter rồi chuyển vòng lập sang cột tiếp theo
Chỉ thế thôi
------------
Vì chỉ cò thể Filter 1 lúc 2 điều kiện, nên nếu anh còn điều kiện nào khác, hãy AutoFilter lần nữa rồi lại xóa tiếp

Nhưng nếu dữ liệu có Merge & Center thì dùng code vẫn xem cell đó là Blank , và giả như dữ liệu đã được autofilter sẵn mà người dùng lại muốn giữ nguyên kiểu dữ liệu như hiện thời đang có(không phá bỏ autofilter đang có) thì phải làm thế nào anh?
Anh xem file đính kèm nhé.

ndu96081631 · 10/6/09

khaivinh đã viết:
Nhưng nếu dữ liệu có Merge & Center thì dùng code vẫn xem cell đó là Blank , và giả như dữ liệu đã được autofilter sẵn mà người dùng lại muốn giữ nguyên kiểu dữ liệu như hiện thời đang có(không phá bỏ autofilter đang có) thì phải làm thế nào anh?
Anh xem file đính kèm nhé.

AutoFilter nó làm việc được với MergeCell mà bạn!... Cũng áp dụng code trên, chỉ là phải xác định lại vùng dử liệu theo kiểu khác (không dùng CurrentRegion được)
Chẳng hạn như:

PHP:

Sub Test()
  Dim i As Long
  With Range([A7], [A65536].End(xlUp)).Resize(, 37)
    For i = 1 To .Columns.Count
      .AutoFilter i, "=", xlOr, 0
      .Offset(1, i - 1).Resize(, 1).SpecialCells(12).ClearContents
      .AutoFilter
    Next
  End With
End Sub

Test thử code này vào file của bạn xem!

khaivinh · 10/6/09

vẫn chưa được vì mình autofilter đã bị phá hủy rồi ,không còn diện mạo như trước nữa
chỉ dùm em cái phần rỗng mang dấu "" nếu mình dùng replace thì what:="""" nó không xóa được ,nên đặt what:= gì mới được nhé.

ndu96081631 · 10/6/09

khaivinh đã viết:
vẫn chưa được vì mình autofilter đã bị phá hủy rồi ,không còn diện mạo như trước nữa
chỉ dùm em cái phần rỗng mang dấu "" nếu mình dùng replace thì what:="""" nó không xóa được ,nên đặt what:= gì mới được nhé.

Không hiểu bạn nói gì?

vì mình autofilter đã bị phá hủy rồi ,không còn diện mạo như trước nữa

Cái này nghĩa là sao?
Việc xóa mấy cell chứa ký tự "" hoặc " " thì có AutoFilter làm ngon lành nhất... Find and Replace không chịu nổi chiêu này đâu! Không tin bạn cứ thử (được ký tự này sẽ bị ký tự khác)
---------
Tôi đưa file lên cho bạn xem này! Tôi xóa sạch láng mấy ký tự ấy bằng AutoFilter rồi đấy! Có vấn đề gì đâu

khaivinh · 11/6/09

Ý mình là lúc đầu file chỉ có vài dòng thôi,mà sau khi xài code xong ,autofilter đã mất và bị sổ ra rất nhiều dòng.Nếu dùng autofilter lại theo điều kiện cũ thì sẽ mất đi row 69(HWR0000129)
Cho nên mình không muốn động đến phần đã được lọc , giờ đang kẹt cái "" không cách nào xóa được nếu không dùng autofilter ^^.
Thôi cám ơn nhiều nhé,mình sẽ học hỏi thêm ;;;;;;;;;;;

Code xoá "rác trắng" trong bảng tính ?

Thành viên tích cực

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

Thành viên tích cực

Huyền thoại GPE

-

Huyền thoại GPE

Thành viên tích cực

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

Huyền thoại GPE

Thành viên tích cực

Huyền thoại GPE

File đính kèm

Thành viên tiêu biểu

Huyền thoại GPE

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

Thành viên tiêu biểu

Thành viên chính thức

File đính kèm

Huyền thoại GPE

Thành viên chính thức

Huyền thoại GPE

File đính kèm

Thành viên chính thức

Đếm ngược thời gian