{"id":6061,"date":"2024-12-21T13:14:50","date_gmt":"2024-12-21T13:14:50","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/loc-danh-sach-khong-trung-bang-vba\/"},"modified":"2024-12-21T13:14:50","modified_gmt":"2024-12-21T13:14:50","slug":"loc-danh-sach-khong-trung-bang-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/loc-danh-sach-khong-trung-bang-vba\/","title":{"rendered":"L\u1ecdc danh s\u00e1ch kh\u00f4ng tr\u00f9ng b\u1eb1ng VBA"},"content":{"rendered":"
\n

Trong c\u00f4ng vi\u1ec7c k\u1ebf to\u00e1n n\u00f3i chung, k\u1ebf to\u00e1n kho n\u00f3i ri\u00eang,\u00a0\u0111\u00f4i khi b\u1ea1n s\u1ebd ph\u1ea3i x\u1eed l\u00fd n\u1ed9i dung sau:<\/p>\n

T\u1eeb b\u1ea3ng ghi s\u1ed5 ph\u00e1t sinh, b\u1ea1n c\u1ea7n ph\u1ea3i l\u1ecdc ra danh s\u00e1ch c\u00e1c m\u1eb7t h\u00e0ng m\u00e0 kh\u00f4ng b\u1ecb tr\u00f9ng, ch\u1ec9 xu\u1ea5t hi\u1ec7n 1 l\u1ea7n (nh\u01b0 trong h\u00ecnh\u00a0\u1ea3nh n\u00e0y)<\/p>\n

<\/p>\n

\n

Xem th\u00eam: Kh\u00f3a\u00a0h\u1ecdc Excel v\u0103n ph\u00f2ng<\/a> t\u1ea1i H\u00e0 N\u1ed9i<\/p>\n<\/blockquote>\n

V\u00e0\u00a0\u0111\u1eb7c bi\u1ec7t h\u01a1n n\u1eefa l\u00e0 l\u00e0m sao ch\u1ec9 v\u1edbi 1 n\u00fat b\u1ea5m ch\u00fang ta\u00a0\u0111\u00e3 l\u1ea5y\u00a0\u0111\u01b0\u1ee3c danh s\u00e1ch n\u00e0y? H\u00e3y c\u00f9ng hocexcel.online t\u00ecm hi\u1ec3u c\u00e1ch \u201cL\u1ecdc \/ t\u1ea1o danh s\u00e1ch kh\u00f4ng tr\u00f9ng b\u1eb1ng\u00a0Excel VBA\u201d n\u00e0y nh\u00e9:<\/p>\n

B\u01b0\u1edbc 1<\/strong>: X\u00e1c\u00a0\u0111\u1ecbnh 2 c\u1ed9t: c\u1ed9t G v\u00e0 c\u1ed9t H\u00a0\u0111\u1ec3 l\u00e0m c\u1ed9t ph\u1ee5, t\u1ea1i\u00a0\u0111\u00e2y:<\/p>\n

C\u1ed9t 1 s\u1ebd d\u00f9ng\u00a0\u0111\u1ec3 th\u1ef1c hi\u1ec7n thao t\u00e1c ph\u00e1t hi\u1ec7n c\u00e1c m\u00e3 kh\u00f4ng b\u1ecb tr\u00f9ng<\/p>\n

C\u1ed9t 2 s\u1ebd\u00a0\u0111\u01b0a c\u00e1c m\u00e3 kh\u00f4ng tr\u00f9ng\u00a0\u0111\u00f3 ra 1 danh s\u00e1ch<\/p>\n

B\u01b0\u1edbc 2<\/strong>: B\u1ea1n m\u1edf c\u1eeda s\u1ed5 VBA v\u00e0 t\u1ea1o 1 Module, g\u00f5 n\u1ed9i dung d\u00f2ng code n\u00e0y:<\/p>\n

\n
\n

D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n


\n
\n<\/a>\n<\/div>\n<\/div>\n

\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n
\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n

<\/p>\n

M\u00ecnh s\u1ebd n\u00f3i chi ti\u1ebft v\u1ec1\u00a0\u00fd ngh\u0129a c\u00e1c d\u00f2ng l\u1ec7nh n\u00e0y nh\u01b0 sau:<\/p>\n

\n

Sub Loc_Ma_Hang()<\/p>\n

\u2026<\/p>\n

End Sub<\/p>\n<\/blockquote>\n

\u0110\u00e2y l\u00e0 1 l\u1ec7nh trong VBA, m\u00e0\u00a0\u1edf\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 x\u00e2y d\u1ef1ng n\u1ed9i dung, tr\u00ecnh t\u1ef1 th\u1ef1c hi\u1ec7n cho c\u00e2u l\u1ec7nh\u00a0\u0111\u00f3 b\u1eb1ng c\u00e1c d\u00f2ng code. C\u1ee5 th\u1ec3\u00a0\u1edf\u00a0\u0111\u00e2y l\u00e0 c\u00e2u l\u1ec7nh gi\u00fap ch\u00fang ta x\u00e2y d\u1ef1ng tr\u00ecnh t\u1ef1 l\u1ecdc m\u00e3 h\u00e0ng kh\u00f4ng tr\u00f9ng trong Sheet1.<\/p>\n

\n

Application.ScreenUpdating = False<\/p>\n

\u2026.<\/p>\n

Application.ScreenUpdating = True<\/p>\n<\/blockquote>\n

D\u00f2ng l\u1ec7nh n\u00e0y cho ph\u00e9p T\u1eaft \/ m\u1edf ch\u1ebf\u00a0\u0111\u1ed9 c\u1eadp nh\u1eadt m\u00e0n h\u00ecnh hi\u1ec3n th\u1ecb c\u1ee7a\u00a0Excel (False l\u00e0 t\u1eaft t\u00ednh n\u0103ng n\u00e0y, True l\u00e0 cho ph\u00e9p ch\u1ea1y ch\u1ee9c n\u0103ng n\u00e0y). Th\u01b0\u1eddng\u00a0\u0111\u01b0\u1ee3c vi\u1ebft d\u01b0\u1edbi d\u1ea1ng T\u1eaft ch\u1ee9c n\u0103ng c\u1eadp nh\u1eadt => Th\u1ef1c hi\u1ec7n c\u00e1c d\u00f2ng l\u1ec7nh kh\u00e1c => Khi th\u1ef1c hi\u1ec7n xong s\u1ebd M\u1edf l\u1ea1i ch\u1ee9c n\u0103ng c\u1eadp nh\u1eadt m\u00e0n h\u00ecnh.<\/p>\n

\n

Dim Lrow As Long
\nLrow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Row<\/p>\n<\/blockquote>\n

T\u00ecm d\u00f2ng cu\u1ed1i c\u1ee7a b\u1ea3ng 1 (Sheet1), t\u00ednh v\u1edbi v\u1ecb tr\u00ed c\u1ed9t th\u1ee9 3 (C\u1ed9t C)<\/p>\n

\n

Sheet1.Range(\u201cH3:H\u201d & Lrow).ClearContents<\/p>\n<\/blockquote>\n

X\u00f3a danh s\u00e1ch l\u1ecdc c\u0169\u00a0\u0111i.\u00a0\u1ede\u00a0\u0111\u00e2y c\u1ed9t H s\u1ebd l\u00e0 n\u01a1i ch\u1ee9a k\u1ebft qu\u1ea3 l\u1ecdc, t\u00ednh t\u1eeb d\u00f2ng th\u1ee9 3<\/p>\n

\n

Sheet1.Range(\u201cG4:G\u201d & Lrow).Formula = \u201c=Countif($C$4:C4,C4)\u201d<\/p>\n<\/blockquote>\n

G\u00e1n v\u00e0o c\u1ed9t G, t\u1eeb d\u00f2ng th\u1ee9 4\u00a0\u0111\u1ebfn d\u00f2ng cu\u1ed1i c\u00f9ng trong b\u1ea3ng c\u00f4ng th\u1ee9c Countif (\u0111\u1ebfm theo\u00a0\u0111i\u1ec1u ki\u1ec7n), v\u00f9ng c\u1ea7n\u00a0\u0111\u1ebfm l\u00e0 t\u1eeb C4\u00a0\u0111\u1ebfn C4 (trong\u00a0\u0111\u00f3 c\u1ed1\u00a0\u0111\u1ecbnh\u00a0\u0111i\u1ec3m\u00a0\u0111\u1ea7u l\u00e0\u00a0\u00f4 C4), gi\u00e1 tr\u1ecb c\u1ea7n\u00a0\u0111\u1ebfm l\u00e0 gi\u00e1 tr\u1ecb t\u1ea1i\u00a0\u00f4 C4<\/p>\n

Khi c\u00f4ng th\u1ee9c n\u00e0y g\u00e1n v\u00e0o c\u00e1c d\u00f2ng ti\u1ebfp theo, th\u00ec\u00a0\u0111i\u1ec3m $C$4 kh\u00f4ng\u00a0\u0111\u1ed5i, c\u00f2n c\u00e1c v\u1ecb tr\u00ed kh\u00e1c s\u1ebd thay\u00a0\u0111\u1ed5i, do\u00a0\u0111\u00f3\u00a0ch\u1ec9 nh\u1eefng d\u00f2ng n\u00e0o cho k\u1ebft qu\u1ea3 =1 th\u00ec m\u1edbi kh\u00f4ng b\u1ecb tr\u00f9ng.<\/p>\n

\n

Sheet1.Range(\u201cG3:G\u201d & Lrow).AutoFilter Field:=1, Criteria1:=1<\/p>\n<\/blockquote>\n

S\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng AutoFilter\u00a0\u0111\u1ec3 l\u1ecdc trong v\u00f9ng G3 t\u1edbi d\u00f2ng cu\u1ed1i c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u, gi\u00e1 tr\u1ecb l\u1ecdc b\u1eb1ng 1 (l\u00e0 c\u00e1c m\u00e3 h\u00e0ng kh\u00f4ng tr\u00f9ng)<\/p>\n

\n

Sheet1.Range(\u201cC4:C\u201d & Lrow).SpecialCells(xlCellTypeVisible).Copy<\/p>\n<\/blockquote>\n

Ch\u1ec9 copy nh\u1eefng gi\u00e1 tr\u1ecb hi\u1ec3n th\u1ecb (kh\u00f4ng b\u1ecb l\u1ecdc b\u1ecf) t\u1ea1i c\u1ed9t C, t\u1eeb d\u00f2ng 4 t\u1edbi d\u00f2ng cu\u1ed1i c\u1ee7a b\u1ea3ng<\/p>\n

\n

Sheet1.Range(\u201cH3\u201d).PasteSpecial xlPasteValues<\/p>\n<\/blockquote>\n

D\u00e1n n\u1ed9i dung v\u1eeba copy v\u00e0o\u00a0\u00f4 H3<\/p>\n

\n

Application.CutCopyMode = False<\/p>\n<\/blockquote>\n

Sau khi\u00a0\u0111\u00e3 paste xong s\u1ebd b\u1ecf ch\u1ee9c n\u0103ng copy<\/p>\n

\n

Sheet1.Range(\u201cG3:G\u201d & Lrow).AutoFilter<\/p>\n<\/blockquote>\n

B\u1ecf ch\u1ee9c n\u0103ng filter\u00a0\u0111i<\/p>\n

\n

Sheet1.Range(\u201cG3:G\u201d & Lrow).ClearContents<\/p>\n<\/blockquote>\n

B\u1ecf c\u00f4ng th\u1ee9c l\u1ecdc\u00a0\u1edf c\u1ed9t G\u00a0\u0111i<\/p>\n

Nh\u01b0 v\u1eady thao t\u00e1c l\u1ecdc\u00a0\u0111\u00e3 ho\u00e0n th\u00e0nh.<\/p>\n

\n

H\u1ecdc Excel k\u1ebf to\u00e1n<\/a> v\u1edbi c\u00e1ch chuy\u00ean gia<\/p>\n<\/blockquote>\n

Ti\u1ebfp\u00a0\u0111\u1ebfn ch\u00fang ta g\u00e1n Sub n\u00e0y v\u00e0o 1 n\u00fat b\u1ea5m, s\u1eed d\u1ee5ng\u00a0Insert\/Sharp\u00a0\u0111\u1ec3 v\u1ebd 1 h\u00ecnh ch\u1eef nh\u1eadt.<\/p>\n

<\/p>\n

Click chu\u1ed9t ph\u1ea3i v\u00e0o h\u00ecnh ch\u1eef nh\u1eadt, b\u1ea5m ch\u1ecdn\u00a0Assign Macro<\/p>\n

Trong c\u1eeda s\u1ed5\u00a0Assign Macro, b\u1ea5m ch\u1ecdn Macro Name l\u00e0 t\u00ean Sub m\u00e0 ch\u00fang ta\u00a0\u0111\u00e3 t\u1ea1o.<\/p>\n

B\u1ea5m ok\u00a0\u0111\u1ec3\u00a0\u0111\u00f3ng c\u1eeda s\u1ed5\u00a0Assign Macro<\/p>\n

B\u00e2y gi\u1edd ch\u00fang ta b\u1ea5m v\u00e0o h\u00ecnh ch\u1eef nh\u1eadt, to\u00e0n b\u1ed9 danh s\u00e1ch m\u00e3 h\u00e0ng kh\u00f4ng tr\u00f9ng\u00a0\u0111\u00e3\u00a0\u0111\u01b0\u1ee3c l\u1ecdc ra t\u1ea1i c\u1ed9t H<\/p>\n

T\u1ea1i\u00a0\u00f4 danh s\u00e1ch m\u00e3 h\u00e0ng (\u00f4 J1), ch\u00fang ta b\u1ea5m ch\u1ecdn ch\u1ee9c n\u0103ng Data \/ Validation<\/p>\n

<\/p>\n

Trong c\u1eeda s\u1ed5 Data Validation, m\u1ee5c Allow ch\u1ecdn List => M\u1ee5c Source ch\u1ecdn ph\u1ea7n danh s\u00e1ch\u00a0\u1edf c\u1ed9t H (c\u00f3 th\u1ec3 ch\u1ecdn ph\u1ea7n Source n\u00e0y l\u1edbn h\u01a1n n\u1ed9i dung danh s\u00e1ch\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 d\u1ef1 ph\u00f2ng nh\u1eefng m\u00e3 h\u00e0ng ph\u00e1t sinh th\u00eam trong qu\u00e1 tr\u00ecnh nh\u1eadp).<\/p>\n

\n

\u0110\u1eebng b\u1ecf l\u1ee1 b\u1ed9 t\u00e0i li\u1ec7u\u00a0h\u01b0\u1edbng d\u1eabn h\u1ecdc Excel 2016\u00a0<\/a><\/p>\n<\/blockquote>\n<\/div>\n

\n

Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n

T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0\ud83d\udcd6<\/span><\/a><\/p>\n

H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n

HKD H\u1ecdc Excel Online
S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n

\n

\u00a9 document.currentScript.insertAdjacentHTML(‘afterend’, ” + new Intl.DateTimeFormat(document.documentElement.lang, {year: ‘numeric’}).format() + ”); H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\n

D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n


\n
\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":6062,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6061","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/6061","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=6061"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/6061\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/6062"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=6061"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=6061"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=6061"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}