Xóa khoảng trống của List - Validation

[Vankien]

Nhờ mọi người chỉ giúp cách làm sao xoá đi những khoảng trống trong List của Validation. Ví dụ: Từ A1:A5 tôi có các danh mục hàng nhưng trong ô A2 và A3 lại không có dữ liệu, khi dùng List của Validation thì những khoảng trống trong vùng A1:A5 vẫn còn, vậy làm sao để xoá đi những khoảng trống đó, mong mọi người giúp đỡ. Thanks !

 

[Mr Okebab]

Không có cách nào khác nếu khoảng trống nằm ở các ô giữa của mảng.
Bạn nên tối ưu lại bằng cách đặt name động nhé.
Thân!

 

[ngothehoahanoi]

hii! Tất nhiên là vẫn có thể làm được....hic, nhưng phải mất công lập trình lắm...!

 

[Mr Okebab]

Thế thì lại phải dựa vào lập trình rồi. Hơi bị mỏi mệt đấy.
Ta chỉ nên lập trình khi các công cụ của excel giải quyết quá tệ vấn đề đó.

Thân!

 

[HHN]

Tích vào ô "ignore blank" là được mà.Chúc thành công.

 

[chibi]

hnn nhầm rồi. ngothehoahanoi nên đưa ra giải pháp cụ thể.

 

[Vankien]

Vậy là ko còn cách nào khác ư, liệu dùng Combobox có làm được ko nhỉ ? ko được nữa thì phải nhờ đến VBA rồi, cảm ơn mọi người !

 

[Mr Okebab]

Không phải là không được. Có 2 cách :
C1 : Tối ưu hóa List của mình
C2: Dùng VBA

Thân!

 

[ngothehoahanoi]

Minh xin đưa code nên các bạn tham khảo nhé:

Sub MyValidation(ByVal KeyCode As Long)
Dim strText As String, strList As String
If Not TypeOf Selection Is Range Then Exit Sub

strText = Selection.Value & Chr(KeyCode)
strList = MakeArr(strText)
Selection.Value = strText
If strList = "False" Then
Selection.Validation.Delete
Else
With Selection.Validation
.Delete
.Add 3, 1, 1, Formula1:=strList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
End With
End If
End Sub

Function MakeArr(ByVal strChr As String) As String
Dim a As Variant
a = [MyList].Value
For i = LBound(a) To UBound(a)
If InStr(1, a(i, 1), strChr, vbTextCompare) = 1 Then
MakeArr = MakeArr & a(i, 1) & Chr(&H2C)
End If
Next
If MakeArr <> "" Then
MakeArr = Left(MakeArr, Len(MakeArr) - 1)
Else
MakeArr = "False"
End If
End Function

 

[adam_tran]

Sử dụng Name động

Bạn tham khảo thêm cách sử dụng Name động để giải quyết việc này nhé. Riêng trong trường hợp này bạn làm như sau:
Thông thường, bạn gán List bằng 1 tên, thí dụ: MyList có ReferTo là A1:A100, nếu chỉ có 50 giá trị trong list thì bạn có đến 50 ô trống.
Thay giá trị trong ReferTo bằng biểu thức:
OFFSET(A1, 0, 0, COUNTA($A$1:$A$100)) bạn sẽ được như ý.
Name động còn có rất nhiều ứng dụng khác. Trong trường hợp Validation, thí bạn muốn List của mình phải thay đổi phụ thuộc vào 1 giá trị khác. Thí dụ cột A là số tài khoản, cột B bạn muốn Validation phải cho ra List chi tiết đối tượng của tài khoản, công nợ chẳng hạn.
Tham khảo file sau để tìm hiểu thêm Validation và Name động nâng cao.

 

[Tuấn Giang]

Bổ sung một chút. Không mới, nhưng dùng được. Đoạn code này mình có mượn của Mr.Hieu và chỉnh sửa. Các bạn có thể tham khảo.

Best Regards,

 

[voquangsu]

Có 1 cách góp vui

Mình thấy vấn đề ở đây là list từ A1:A5 mà ô A2 và A3 trống. Mình có thể giải quyết mà không cần dùng đến VBA. Mình gửi ví dụ kèm theo

 

[andredat]

ô trống trong list validation

Bạn tham khảo thêm cách sử dụng Name động để giải quyết việc này nhé. Riêng trong trường hợp này bạn làm như sau:
Thông thường, bạn gán List bằng 1 tên, thí dụ: MyList có ReferTo là A1:A100, nếu chỉ có 50 giá trị trong list thì bạn có đến 50 ô trống.
Thay giá trị trong ReferTo bằng biểu thức:
OFFSET(A1, 0, 0, COUNTA($A$1:$A$100)) bạn sẽ được như ý.
Name động còn có rất nhiều ứng dụng khác. Trong trường hợp Validation, thí bạn muốn List của mình phải thay đổi phụ thuộc vào 1 giá trị khác. Thí dụ cột A là số tài khoản, cột B bạn muốn Validation phải cho ra List chi tiết đối tượng của tài khoản, công nợ chẳng hạn.
Tham khảo file sau để tìm hiểu thêm Validation và Name động nâng cao.

Mình không thạo VBA nên mình cũng có cách làm thủ công. Mong nhận được góp ý