Code xoá "rác trắng" trong bảng tính ? (1 người xem)

[TrungChinhs]

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ụ:

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]

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

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

 

[TrungChinhs]

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.

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]

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.

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]

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:

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]

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

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]

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]

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]

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]

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]

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:

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]

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:

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]

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]

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):

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

 

[rollover79]

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):

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]

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:

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]

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ư:

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]

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]

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]

Ý 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

 

[ndu96081631]

Ý 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

Tôi hiểu ý bạn! Cũng đã xem lại file mà chẳng hiểu tại sao người ta lọc được như thế!
Hay là thế này: Bạn thử Show all dử liệu, xong Filter lại bằng tay xem có được như thế không?
Nếu bạn có thể làm bằng tay được vụ này thì tôi sẽ viết lại code, bảo tồn mọi thứ đã Filter cho bạn!

 

[khaivinh]

hì hì ,đó là vì autofilter trước rồi mới thêm vào dữ liệu cho row 69 .

 

[ndu96081631]

hì hì ,đó là vì autofilter trước rồi mới thêm vào dữ liệu cho row 69 .

Ẹc... Ẹc... Vậy cũng nói!
Thế thì trước khi chạy code, bạn cut dong 69 ấy mang đi nơi khác ---> Chạy code xong, bạn lại AutoFilter theo điều kiện ban đầu và dời dử liệu đã chuyển vào dòng 69 (cũng như bạn đã từng nhập liệu sau khi Filter vậy) ---> Có khó khăn gì đâu

 

[khaivinh]

vì file đó là của người khác kéo từ hệ thống ra,bản chất nó đã vậy, mình muốn dùng code để xác định PrintArea,và mình dùng subtotal(3,...) để xác định giới hạn của column và dùng End(xlUp) để xác định row ,Nhưng lại gặp dữ liệu rác nên counta không xác định đúng mục tiêu

Sub vinh()
Dim i As Byte
Dim j As Long
Dim rng As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For i = 1 To 100
If Application.WorksheetFunction.Subtotal(3, Range(Cells(1, i), Cells(500, i))) = 0 Then Exit For
Next
i = i
j = [a65500].End(xlUp).Row

With ActiveSheet.PageSetup
.PrintArea = Range([a1], Cells(j + 1, i + 1)).Address
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0)
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = True
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Mình lấy thí dụ như file đính kèm, anh xem giúp nhé

 

[Phanhanhdai]

Đú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!

Như vậy, đến đây về cơ bản cách của thày ndu dùng Auto Filter tương đối là OK, chỉ lăn tăn có thể có trường hợp cả dữ liệu thô và có công thức lẫn lộn nhau thôi. Em chưa có kỹ năng nhiều, nhưng em nghĩ giữa dữ liệu thô (Value) và công thức nó khác nhau ở dấu bằng (=), nếu dựa vào cái này có thể nhận biết, giải quyết vấn đề được không ah?

 

[TrungChinhs]

Như vậy, đến đây về cơ bản cách của thày ndu dùng Auto Filter tương đối là OK, chỉ lăn tăn có thể có trường hợp cả dữ liệu thô và có công thức lẫn lộn nhau thôi. Em chưa có kỹ năng nhiều, nhưng em nghĩ giữa dữ liệu thô (Value) và công thức nó khác nhau ở dấu bằng (=), nếu dựa vào cái này có thể nhận biết, giải quyết vấn đề được không ah?

Được chứ, để chọn ô dữ liệu Value ta sử dụng Specialcells(2) còn để chọn ô dữ liệu Function ta sử dụng Specialcells(3)
Bạn tìm hiểu Specialcells tại đây http://www.giaiphapexcel.com/forum/...ting-the-Most-Out-of-Ranges&p=63004#post63004

 

[Phanhanhdai]

Thế thì bài toán được giải quyết triệt để mà chẳng cần Code gì phức tạp, mình kết hợp AutoFilter với Go to, cụ thể:
- Đầu tiên mình dùng Auto Filter lọc ra những ô trắng và những ô không (Bước này chưa phân biệt dữ liệu thô hay không).
- Bước 2 bôi đen tất cả vùng lọc tại bước 1 thực hiện Ctrl + G/Special/Constants/delete là xong

 

[TrungChinhs]

Thế thì bài toán được giải quyết triệt để mà chẳng cần Code gì phức tạp

Phần nhiều câu lệnh trong VBA là thực hiện các lệnh tương tự như ta làm thủ công. Người ta nghiên cứu viết code là để tự động hóa các công việc đó khi ứng dụng cho những trường hợp dữ liệu lớn (nhiều dòng, nhiều cột) mà nếu làm thủ công thì sẽ mất nhiều thời gian hơn và đặc biệt khi bạn thiết kế một chương trình mà gặp trường hợp này nếu không dùng VBA thì chương trình của bạn tèo luôn.

 

[Phanhanhdai]

Phần nhiều câu lệnh trong VBA là thực hiện các lệnh tương tự như ta làm thủ công. Người ta nghiên cứu viết code là để tự động hóa các công việc đó khi ứng dụng cho những trường hợp dữ liệu lớn (nhiều dòng, nhiều cột) mà nếu làm thủ công thì sẽ mất nhiều thời gian hơn và đặc biệt khi bạn thiết kế một chương trình mà gặp trường hợp này nếu không dùng VBA thì chương trình của bạn tèo luôn.

Vâng, quả là từng trường hợp mà ta áp dụng. Xin bác cho em hỏi đến giờ đã có Code VBA nào hoàn chỉnh đáp ứng yêu cầu của Chủ đề mà tránh được Funtion chưa ah?