Code ẩn dòng rỗng không dùng vòng lặp.

[TrungChinhs]

Chia xẻ với các bạn Code ẩn dòng rỗng không dùng vòng lặp (trong file ví dụ đính kèm)
đơn giản, dễ hiểu ai cũng có thể vận dụng được

Quy ước dòng rỗng là dòng chưa sử dụng: không có ký tự (kể cả ký tự trắng), không có công thức, không có Comments (ghi chú)

Bạn có thể dễ dàng chỉnh sửa vùng cần ẩn bằng cách chỉnh lại số dòng chỉ vùng và chọn loại dòng cần ẩn theo ý muốn bằng cách đánh dấu nháy vào đầu dòng lệnh trong code. Muốn loại dòng nào ẩn thì đánh dấu nháy vào đầu dòng đó.

Nếu thấy hay thì nhấn chuột vào nút Cảm ơn nha!

 

[Hoàng Trọng Nghĩa]

Quy ước dòng rỗng là dòng chưa sử dụng: không có ký tự (kể cả ký tự trắng), không có công thức, không có Comments (ghi chú)

Cảm ơn ANH TRUNG CHINH rât nhiều! Phải chi quy ước cho nó ẩn luôn cả công thức = 0 hoặc "" thì hay quá!
Để em thử dùng ...HasFormulars = 0 or "" có được không.

 

[tranttoan]

Cảm ơn bài viết của Bác Trung Chinh, bác vui lòng cho mình hỏi có cách nào ẩn dòng rổng ở Sheet1 khi Sheet1 có công thức lấy dữ liệu từ Sheet2 không ? vì khi có công thức thì code không chạy như ý
VD : Nhập ở Sheet1

=IF(Sheet2!A4<>"",Sheet2!A4,"")

 

[TrungChinhs]

Ẩn dòng trắng

Cảm ơn ANH TRUNG CHINH rât nhiều! Phải chi quy ước cho nó ẩn luôn cả công thức = 0 hoặc "" thì hay quá!
Để em thử dùng ...HasFormulars = 0 or "" có được không.

Thử lại "củ khoai" này xem sao. Tôi đã bổ sung đoạn code ẩn dòng công thức có giá trị 0 và dòng có ký tự trắng.

Cái này chính xác phải gọi là ẩn dòng trắng chứ không chỉ là ẩn dòng rỗng.

Các bạn thử kỹ xem có sai sót gì không (tôi chỉ mới test sơ qua).

P/s các bạn chú ý code này làm việc từ dòng 5 đến dòng 10.000 nếu bạn muốn điều chỉnh dòng đầu thì vào code và dùng Replace để sửa. vd dữ liệu của bạn bắt đầu từ dòng 6 thì bạn nhấn Crtl + H -> khung trên gõ số 5, khung dứơi gõ số 6 -> nhấn OK

 

[tranttoan]

Thử lại "củ khoai" này xem sao. Tôi đã bổ sung đoạn code ẩn dòng công thức có giá trị 0 và dòng có ký tự trắng.

Cái này chính xác phải gọi là ẩn dòng trắng chứ không chỉ là ẩn dòng rỗng.

Các bạn thử kỹ xem có sai sót gì không (tôi chỉ mới test sơ qua).

P/s các bạn chú ý code này làm việc từ dòng 5 đến dòng 10.000 nếu bạn muốn điều chỉnh dòng đầu thì vào code và dùng Replace để sửa. vd dữ liệu của bạn bắt đầu từ dòng 6 thì bạn nhấn Crtl + H -> khung trên gõ số 5, khung dứơi gõ số 6 -> nhấn OK

Cảm ơn Bác Trung Chinh nhé, tôi đã thử test sơ qua thấy " Củ khoai " này ngon lành, tôi sẽ thử trong Fỉle chính có gì sẽ phản hồi cho Bác.
Cảm ơn bác nhiều !
Thân chào bác

 

[chibi]

Dữ liệu nằm ở sau cột FB (từ FC đến IV) thì thế nào nhỉ?

 

[TrungChinhs]

Nếu dữ liệu lớn hơn 158 cột (cột FB) thì phải chỉnh rộng ra tương tự như chỉnh dòng còn lý do tại sao tôi không để tại cột IV vì nó sẽ ảnh hưởng đến việc thêm bớt, ẩn hiện cột.
Nhưng phần ẩn ký tự trắng code này chưa ổn nếu dòng không có ký tự number thì ký tự text đen nó cũng ẩn luôn. Bó tay.com

 

[ndu96081631]

Code cùa anh rất hay nhưng em nghĩ đã là dử liệu thì phải có cấu trúc --> Và đương nhiên cấu trúc ấy ta đã biết trước, đúng không?
Trong trường hợp này em nghĩ dùng Advanced Filter sẽ nhanh hơn rất nhiều
Anh xem file giả lập em đính kèm đây, code chỉ có 1 dòng:

Private Sub Worksheet_Activate()
  Range("C8").CurrentRegion.AdvancedFilter 1, [K8:K9]
End Sub

Với Advanced Filter thì chỉ cần đạt được điều kiện là nó "mần"... nó không hề quan tâm cell ấy là công thức hay dử liệu thô
Mấu chốt vấn đề nằm ở chổ: Nếu ta viết được công thức cho cell điều kiện thì hoàn toàn có thể bắt Advanced Filter lọc theo ý mình

 

[TrungChinhs]

Code cùa anh rất hay nhưng em nghĩ đã là dử liệu thì phải có cấu trúc --> Và đương nhiên cấu trúc ấy ta đã biết trước, đúng không?
Trong trường hợp này em nghĩ dùng Advanced Filter sẽ nhanh hơn rất nhiều
Anh xem file giả lập em đính kèm đây, code chỉ có 1 dòng:

Private Sub Worksheet_Activate()
  Range("C8").CurrentRegion.AdvancedFilter 1, [K8:K9]
End Sub

Với Advanced Filter thì chỉ cần đạt được điều kiện là nó "mần"... nó không hề quan tâm cell ấy là công thức hay dử liệu thô
Mấu chốt vấn đề nằm ở chổ: Nếu ta viết được công thức cho cell điều kiện thì hoàn toàn có thể bắt Advanced Filter lọc theo ý mình

1. Vì bảng dữ liệu có thể không liên tục (xen kẽ dòng rỗng, cột rỗng) thì không CurrentRegion được ndu à.
2. Cái đau đầu nhất hiện nay là làm thế nào phân biệt được được ô ký đen và ô ký tự trắng như thằng Autofilter ấy ? ác cái là Autofilter chỉ được từng cột 1 nên hậu Autofilter không giải quyết được cái chung
3. Hôm qua mình có nghĩ đến phương pháp tìm bằng màu nền và có xem 1 số bài trên diễn đàn (trong đó có cả bài của Tuấn) nhưng mình chưa hiểu nên không biết cách này có được không ?

 

[ndu96081631]

1. Vì bảng dữ liệu có thể không liên tục (xen kẽ dòng rỗng, cột rỗng) thì không CurrentRegion được ndu à.

File của em chỉ là ví dụ thôi... như em đã nói, dử liệu phải có cấu trúc, và cấu trúc này đương nhiên anh đã biết trước ---> Anh thay nó vào CurrentRegion là được rồi

2. Cái đau đầu nhất hiện nay là làm thế nào phân biệt được được ô ký đen và ô ký tự trắng như thằng Autofilter ấy ? ác cái là Autofilter chỉ được từng cột 1 nên hậu Autofilter không giải quyết được cái chung

Em chưa hiểu ô ký đen và ô ký tự trắng nghĩa là gì?

 

[TrungChinhs]

Em chưa hiểu ô ký đen và ô ký tự trắng nghĩa là gì?

à ký tự đen là ký tự a -> z hiện thị ta nhìn thấy được còn ký tự trắng là Ký tự rỗng hoặc BLanks như: ""; " "; "___ ". (cái này mình gọi tắt như vậy để phân biệt 2 loại ký tự này thôi)

 

[TrungChinhs]

Code ẩn dòng không có dữ liệu

Hôm qua tôi có chia xẻ với các bạn code ẩn dòng rỗng không dùng vòng lặp nhưng một số bạn có yêu cầu ẩn cả dòng công thức có giá trị bằng 0 và dòng có ký tự trắng ("" hoặc " " hoặc "__"). Tối qua tôi có gửi File bổ sung nhưng sáng nay kiểm tra lại code không đạt yêu cầu khi xử lý dữ liệu kiểu Text và tính tuỳ biến còn hạn chế.

Tôi gửi lại các bạn File có 2 gồm 2 sub:
1. Code ẩn dòng rỗng không dùng vòng lặp (đã chỉnh sửa lại cho tuỳ biến hơn).
2. Code ẩn các dòng không hiển thị dữ liệu (bao gồm dòng rỗng, dòng có chứa công thức mà giá trị = 0, dòng có chứa các ký tự rỗng hoặc ký tự trắng).

Lưu ý: Code này dò tìm số 1 để xác định đầu vùng dữ liệu (không tính dòng tiêu đề cột) do đó STT phải bắt đầu là số 1 và bảng tính chỉ được sử dụng từ cột B trở đi.

 

[phuongc5]

Em có vấn đề cũng là ẩn nhưng là ẩn cột có điều kiện. Tại file đính kèm, những ô có nền vàng là ô em định dạng trong Conditonal Formatting: =subtotal(9,H5:H150)=0, thoả mã điều kiện này thì sẽ hiện nền màu vàng. Bây giờ em muốn những cột mà có ô màu vàng này thì sẽ bị ẩn đi (vì cột rỗng mà). Rất mong được mọi người giúp đỡ!

 

[ndu96081631]

Hôm qua tôi có chia xẻ với các bạn code ẩn dòng rỗng không dùng vòng lặp nhưng một số bạn có yêu cầu ẩn cả dòng công thức có giá trị bằng 0 và dòng có ký tự trắng ("" hoặc " " hoặc "__"). Tối qua tôi có gửi File bổ sung nhưng sáng nay kiểm tra lại code không đạt yêu cầu khi xử lý dữ liệu kiểu Text và tính tuỳ biến còn hạn chế.

Tôi gửi lại các bạn File có 2 gồm 2 sub:
1. Code ẩn dòng rỗng không dùng vòng lặp (đã chỉnh sửa lại cho tuỳ biến hơn).
2. Code ẩn các dòng không hiển thị dữ liệu (bao gồm dòng rỗng, dòng có chứa công thức mà giá trị = 0, dòng có chứa các ký tự rỗng hoặc ký tự trắng).

Lưu ý: Code này dò tìm số 1 để xác định đầu vùng dữ liệu (không tính dòng tiêu đề cột) do đó STT phải bắt đầu là số 1 và bảng tính chỉ được sử dụng từ cột B trở đi.

Anh ơi cái này em thấy vẫn dùng Advanced Filter được như thường
Như em nói trước, cái quan trọng là:
- Cấu trúc dử liệu anh đã xác định trước
- Có thể viết được công thức thể hiện điều kiện lọc
Với file của anh thì:
- Vùng dử liệu được xác định
Range([D7], [D65536].End(xlUp)).Resize(, 17)
- Công thức thể hiện điều kiện lọc

=COUNTIF($E8:$T8," ")+COUNTIF($E8:$T8,0)+COUNTIF($E8:$T8,"")<16

Công thức này nằm tại cell T2
Vậy code của em chỉ là:

Sub Hide()
  On Error Resume Next: UnHide
  Range([D7], [D65536].End(xlUp)).Resize(, 17).AdvancedFilter 1, [T1:T2]
End Sub

Sub UnHide()
  On Error Resume Next
  ActiveSheet.ShowAllData
End Sub

 

[ndu96081631]

Em có vấn đề cũng là ẩn nhưng là ẩn cột có điều kiện. Tại file đính kèm, những ô có nền vàng là ô em định dạng trong Conditonal Formatting: =subtotal(9,H5:H150)=0, thoả mã điều kiện này thì sẽ hiện nền màu vàng. Bây giờ em muốn những cột mà có ô màu vàng này thì sẽ bị ẩn đi (vì cột rỗng mà). Rất mong được mọi người giúp đỡ!

Như file của bạn, tại sheet TH, ta có thể duyệt dòng 152, trong nhưng cell có công thức ---> Cell nào có giá trị = 0 thì ẩn cột ---> Được chứ

Sub Test()
  Dim Clls As Range
  For Each Clls In Range("152:152").SpecialCells(3)
    Clls.EntireColumn.Hidden = (Clls = 0)
  Next
End Sub

 

[TrungChinhs]

Anh ơi cái này em thấy vẫn dùng Advanced Filter được như thường
Như em nói trước, cái quan trọng là:
- Cấu trúc dử liệu anh đã xác định trước
- Có thể viết được công thức thể hiện điều kiện lọc
Với file của anh thì:
- Vùng dử liệu được xác định
Range([D7], [D65536].End(xlUp)).Resize(, 17)
- Công thức thể hiện điều kiện lọc

=COUNTIF($E8:$T8," ")+COUNTIF($E8:$T8,0)+COUNTIF($E8:$T8,"")<16

Công thức này nằm tại cell T2
Vậy code của em chỉ là:

Sub Hide()
  On Error Resume Next: UnHide
  Range([D7], [D65536].End(xlUp)).Resize(, 17).AdvancedFilter 1, [T1:T2]
End Sub

Sub UnHide()
  On Error Resume Next
  ActiveSheet.ShowAllData
End Sub

@ ndu: Giải pháp của Bạn thật tuyệt nhưng tôi vẫn muốn Bạn viết thêm một chút cho nó động (thêm hoặc bớt dòng, cột nó vẫn chạy đúng).
Chờ bài của Bạn.
Thân !

 

[ndu96081631]

@ ndu: Giải pháp của Bạn thật tuyệt nhưng tôi vẫn muốn Bạn viết thêm một chút cho nó động (thêm hoặc bớt dòng, cột nó vẫn chạy đúng).
Chờ bài của Bạn.
Thân !

Cái này em nghĩ anh tự làm được mà! ---> Có thể tạo 1 name động (trong Define name) cho vùng dử liệu, và công thức sẽ tham chiếu đến vùng này chẳng hạn!... vân vân... và nhiều cách khác nữa (như xlUp, xlRighttoLeft trong VBA)
Mấu chốt cuối cùng vẫn là:
- Xác định được vùng dử liệu
- Viết được công thức điều kiện
Phần còn lại (ẩn dòng) sẽ do Advanced Filter thực hiện, vì theo em cách này đơn giản nhất

 

[trungvdb]

Code của thày rất hay, em chưa hiểu 16 ở đây là gì thế ah.

=COUNTIF($E8:$T8," ")+COUNTIF($E8:$T8,0)+COUNTIF($E8:$T8,"")<16

---------
Em xin lỗi vì em chưa quan sát kỹ bảng bao gồm 16 cột