{"id":5666,"date":"2024-12-18T19:07:08","date_gmt":"2024-12-18T19:07:08","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/18\/huong-dan-cach-loc-du-lieu-vao-bao-cao-chi-tiet-trong-excel-bang-vba\/"},"modified":"2024-12-18T19:07:08","modified_gmt":"2024-12-18T19:07:08","slug":"huong-dan-cach-loc-du-lieu-vao-bao-cao-chi-tiet-trong-excel-bang-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/18\/huong-dan-cach-loc-du-lieu-vao-bao-cao-chi-tiet-trong-excel-bang-vba\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch l\u1ecdc d\u1eef li\u1ec7u v\u00e0o B\u00e1o c\u00e1o chi ti\u1ebft trong Excel b\u1eb1ng VBA"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p>Trong c\u00f4ng vi\u1ec7c, b\u1ea1n c\u00f3 t\u1eebng g\u1eb7p ph\u1ea3i nh\u1eefng c\u00e2u h\u1ecfi n\u00e0y kh\u00f4ng:<\/p>\n<ul>\n<li>L\u1eadp b\u00e1o c\u00e1o chi ti\u1ebft nh\u01b0 th\u1ebf n\u00e0o?<\/li>\n<li>L\u00e0m th\u1ebf n\u00e0o\u00a0\u0111\u1ec3 l\u1ea5y d\u1eef li\u1ec7u t\u1eeb m\u1ed9t b\u1ea3ng theo nh\u1eefng y\u00eau c\u1ea7u nh\u1ea5t\u00a0\u0111\u1ecbnh?<\/li>\n<li>C\u00e1ch tr\u00edch xu\u1ea5t d\u1eef li\u1ec7u theo\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u0111\u1ec3\u00a0\u0111\u01b0a sang 1 b\u1ea3ng kh\u00e1c?<\/li>\n<\/ul>\n<p>B\u00e0i vi\u1ebft sau\u00a0\u0111\u00e2y s\u1ebd gi\u00fap b\u1ea1n tr\u1ea3 l\u1eddi nh\u1eefng c\u00e2u h\u1ecfi\u00a0\u0111\u00f3.\u00a0\u0110\u1ed3ng th\u1eddi b\u1ea1n c\u0169ng c\u00f3 th\u1ec3 kh\u00e1m ph\u00e1 ra m\u1ed9t c\u00e1ch l\u00e0m m\u1edbi r\u1ea5t hay khi s\u1eed d\u1ee5ng VBA v\u00e0o vi\u1ec7c l\u1ecdc d\u1eef li\u1ec7u v\u00e0o B\u00e1o c\u00e1o chi ti\u1ebft.<\/p>\n<p><strong>* Ch\u00fang ta c\u00f3 y\u00eau c\u1ea7u sau: D\u1ef1a v\u00e0o b\u1ea3ng d\u01b0\u1edbi\u00a0\u0111\u00e2y, h\u00e3y l\u1ea5y d\u1eef li\u1ec7u\u00a0\u1edf B\u1ea3ng chi ti\u1ebft d\u1ef1a theo\u00a0\u0111i\u1ec1u ki\u1ec7n thay\u00a0\u0111\u1ed5i\u00a0\u1edf c\u00e1c\u00a0\u00f4 t\u1eeb I2:I4 (Th\u1eddi gian v\u00e0 nh\u00e0 cung c\u1ea5p)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12594 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic2.png\" width=\"1374\" height=\"702\" title=\"pic2\"><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-67631d5b66e4b\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Buoc_1_Ghi_macro_cac_thao_tac\" title=\"B\u01b0\u1edbc 1: Ghi macro c\u00e1c thao t\u00e1c\">B\u01b0\u1edbc 1: Ghi macro c\u00e1c thao t\u00e1c<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Buoc_2_Doc_noi_dung_macro\" title=\"B\u01b0\u1edbc 2:\u00a0\u0110\u1ecdc n\u1ed9i dung macro\">B\u01b0\u1edbc 2:\u00a0\u0110\u1ecdc n\u1ed9i dung macro<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Buoc_3_Tinh_gon_macro_de_su_dung\" title=\"B\u01b0\u1edbc 3: Tinh g\u1ecdn\u00a0macro\u00a0\u0111\u1ec3 s\u1eed d\u1ee5ng\">B\u01b0\u1edbc 3: Tinh g\u1ecdn\u00a0macro\u00a0\u0111\u1ec3 s\u1eed d\u1ee5ng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Buoc_4_Copy_du_lieu_va_paste_sang_bang_chi_tiet_va_hoan_thien_code\" title=\"B\u01b0\u1edbc 4:\u00a0Copy d\u1eef li\u1ec7u v\u00e0 paste sang b\u1ea3ng chi ti\u1ebft v\u00e0 ho\u00e0n thi\u1ec7n code\">B\u01b0\u1edbc 4:\u00a0Copy d\u1eef li\u1ec7u v\u00e0 paste sang b\u1ea3ng chi ti\u1ebft v\u00e0 ho\u00e0n thi\u1ec7n code<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Buoc_5_Kiem_tra_code_bang_phim_F8\" title=\"B\u01b0\u1edbc 5: Ki\u1ec3m tra code b\u1eb1ng ph\u00edm F8\">B\u01b0\u1edbc 5: Ki\u1ec3m tra code b\u1eb1ng ph\u00edm F8<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Buoc_6_Gan_macro_vao_su_kien_thay_doi_dieu_kien_o_vung_o_I2_I4\" title=\"B\u01b0\u1edbc 6: G\u00e1n macro v\u00e0o s\u1ef1 ki\u1ec7n thay\u00a0\u0111\u1ed5i\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u1edf v\u00f9ng \u00f4 I2:I4\">B\u01b0\u1edbc 6: G\u00e1n macro v\u00e0o s\u1ef1 ki\u1ec7n thay\u00a0\u0111\u1ed5i\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u1edf v\u00f9ng \u00f4 I2:I4<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/su-dung-vba-de-loc-du-lieu-vao-bao-cao-chi-tiet.html#Ket_luan\" title=\"K\u1ebft\u00a0 lu\u1eadn\">K\u1ebft\u00a0 lu\u1eadn<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_1_Ghi_macro_cac_thao_tac\"><\/span><strong>B\u01b0\u1edbc 1: Ghi macro c\u00e1c thao t\u00e1c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1ea1i tab Developer, c\u00e1c b\u1ea1n ch\u1ecdn n\u00fat l\u1ec7nh Record Macro<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12596 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic3-2.png\" width=\"702\" height=\"534\" title=\"pic3-2\"><\/p>\n<ul>\n<li>Thao t\u00e1c 1: Ch\u1ecdn ch\u1ee9c n\u0103ng Data \/ Filter cho b\u1ea3ng d\u1eef li\u1ec7u<\/li>\n<li>Thao t\u00e1c 2: L\u1ecdc c\u1ed9t Nh\u00e0 cung c\u1ea5p theo t\u00ean Nh\u00e0 cung c\u1ea5p\u00a0\u1edf\u00a0\u00f4 I4<\/li>\n<li>Thao t\u00e1c 3: L\u1ecdc ng\u00e0y\u00a0\u1edf c\u1ed9t Ng\u00e0y theo th\u00f4ng tin ng\u00e0y\u00a0\u1edf\u00a0\u00f4 I2 v\u00e0 I3<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12597 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/video-record-macro-loc-du-lieu.gif\" width=\"1212\" height=\"854\" title=\"video-record-macro-loc-du-lieu\"><\/p>\n<div class=\"code-block code-block-3\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<p>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<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-496.gif\" title=\"ex101_92019-nho-496\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-208.gif\" title=\"vba101_92019-nho-208\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-496.gif\" title=\"ex101_92019-nho-496\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<blockquote>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/excel-co-ban\">H\u01b0\u1edbng d\u1eabn h\u1ecdc Excel c\u01a1 b\u1ea3n<\/a><\/p>\n<\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_2_Doc_noi_dung_macro\"><\/span><strong>B\u01b0\u1edbc 2:\u00a0\u0110\u1ecdc n\u1ed9i dung macro<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>M\u1edf c\u1eeda s\u1ed5 VBA, ch\u00fang ta xem n\u1ed9i dung Macro v\u1eeba ghi\u00a0\u0111\u01b0\u1ee3c<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12598 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic4.png\" width=\"938\" height=\"352\" title=\"pic4\"><\/p>\n<blockquote>\n<p>Range(\u201cA2:F2\u201d).Select<br \/>\nSelection.AutoFilter<\/p>\n<\/blockquote>\n<p>N\u1ed9i dung n\u00e0y l\u00e0 : Ch\u1ecdn v\u00f9ng\u00a0\u00f4 t\u1eeb A2\u00a0\u0111\u1ebfn F2, m\u1edf ch\u1ee9c n\u0103ng Auto filter<\/p>\n<blockquote>\n<p>ActiveSheet.Range(\u201c$A$2:$F$47\u2033).AutoFilter Field:=2, Criteria1:=\u201dAnh Tu?n\u201d<\/p>\n<\/blockquote>\n<p>N\u1ed9i dung n\u00e0y l\u00e0: L\u1ecdc d\u1eef li\u1ec7u\u00a0\u1edf c\u1ed9t th\u1ee9 2 (c\u1ed9t NCC),\u00a0\u0111i\u1ec1u ki\u1ec7n l\u1ecdc l\u00e0 \u201cAnh Tu\u1ea5n\u201d =&gt;\u00a0VBA kh\u00f4ng h\u1ed7 tr\u1ee3 ti\u1ebfng vi\u1ec7t\u00a0\u0111\u1ea7y\u00a0\u0111\u1ee7 n\u00ean c\u00f3 d\u1ea5u ?<\/p>\n<blockquote>\n<p>ActiveSheet.Range(\u201c$A$2:$F$47\u201d).AutoFilter Field:=1, Criteria1:= _<br \/>\n\u201c&gt;=01\/10\/2017\u2033, Operator:=xlAnd, Criteria2:=\u201d&lt;=31\/10\/2017\u201d<\/p>\n<\/blockquote>\n<p>N\u1ed9i dung n\u00e0y l\u00e0: L\u1ecdc d\u1eef li\u1ec7u\u00a0\u1edf c\u1ed9t th\u1ee9 1 (c\u1ed9t Ng\u00e0y),\u00a0\u0111i\u1ec1u ki\u1ec7n l\u1ecdc l\u00e0 L\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng ng\u00e0y 01\/10\/2017, v\u00e0 nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng 31\/10\/2017<\/p>\n<blockquote>\n<p>\u0110\u1eebng b\u1ecf l\u1ee1:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-ke-toan.html\">l\u01a1\u0301p h\u1ecdc Excel k\u1ebf to\u00e1n<\/a> v\u1edbi c\u00e1c chuy\u00ean gia<\/p>\n<\/blockquote>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_3_Tinh_gon_macro_de_su_dung\"><\/span><strong>B\u01b0\u1edbc 3: Tinh g\u1ecdn\u00a0macro\u00a0\u0111\u1ec3 s\u1eed d\u1ee5ng<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>C\u1ea7n thay\u00a0\u0111\u1ed5i\u00a0\u0111i\u1ec1u ki\u1ec7n l\u1ecdc trong VBA\u00a0\u0111\u1ec3 li\u00ean k\u1ebft t\u1edbi v\u00f9ng\u00a0\u0111i\u1ec1u ki\u1ec7n trong B\u1ea3ng chi ti\u1ebft =&gt; Khi\u00a0\u0111\u00f3 thay\u00a0\u0111\u1ed5i d\u1eef li\u1ec7u trong B\u1ea3ng chi ti\u1ebft th\u00ec macro s\u1ebd t\u1ef1\u00a0\u0111\u1ed9ng l\u1ecdc theo n\u1ed9i dung\u00a0\u0111\u00f3.<\/p>\n<p>Ch\u00fang ta ch\u00fa\u00a0\u00fd v\u00e0o c\u00e1c v\u00f9ng\u00a0\u0111i\u1ec1u ki\u1ec7n (Criteria1, Criteria2)\u00a0\u1edf trong c\u00e2u l\u1ec7nh trong VBA<\/p>\n<p><strong><em>* C\u00e2u l\u1ec7nh l\u1ecdc NCC<\/em><\/strong><\/p>\n<p>ActiveSheet.Range(\u201c$A$2:$F$47\u2033).AutoFilter Field:=2, Criteria1:=\u201dAnh Tu?n\u201d<\/p>\n<p>Thay \u201cAnh Tu?n\u201d b\u1eb1ng\u00a0\u00f4 I4 (d\u00f2ng 4, c\u1ed9t 9, sheet 1)\u00a0trong sheet1 nh\u01b0 sau:<\/p>\n<blockquote>\n<p>ActiveSheet.Range(\u201c$A$2:$F$47\u201d).AutoFilter Field:=2, Criteria1:=Sheet1.Cells(4, 9).value<\/p>\n<\/blockquote>\n<p><strong><em>* C\u00e2u l\u1ec7nh l\u1ecdc Ng\u00e0y<\/em><\/strong><\/p>\n<p>ActiveSheet.Range(\u201c$A$2:$F$47\u201d).AutoFilter Field:=1, Criteria1:= _<br \/>\n\u201c&gt;=01\/10\/2017\u2033, Operator:=xlAnd, Criteria2:=\u201d&lt;=31\/10\/2017\u201d<\/p>\n<p>Thay c\u00e1c m\u1ee5c\u00a0\u201c&gt;=01\/10\/2017\u201d v\u00e0\u00a0\u201c&lt;=31\/10\/2017\u201d nh\u01b0 sau:<\/p>\n<blockquote>\n<p>ActiveSheet.Range(\u201c$A$2:$F$47\u201d).AutoFilter Field:=1, Criteria1:= _<br \/>\n\u201c&gt;=\u201d &amp; CLng(Sheet1.Cells(2, 9).value), Operator:=xlAnd, Criteria2:=\u201d&lt;=\u201d &amp; CLng(Sheet1.Cells(3, 9).value)<\/p>\n<\/blockquote>\n<p>V\u00ec gi\u00e1 tr\u1ecb ng\u00e0y th\u00e1ng trong\u00a0Excel\u00a0v\u1ec1 b\u1ea3n ch\u1ea5t l\u00e0 d\u1ea1ng\u00a0s\u1ed1 n\u00ean c\u00f3 th\u1ec3\u00a0chuy\u1ec3n\u00a0\u0111\u1ed5i v\u1ec1 d\u1ea1ng CLng(\u2026)<\/p>\n<p><strong><em>* Tinh g\u1ecdn v\u00e0 ho\u00e0n thi\u1ec7n Code VBA<\/em><\/strong><\/p>\n<p>Range(\u201cA2:F2\u201d).Select\u00a0 \u00a0 \u00a0 \u00a0+\u00a0 \u00a0 Selection.AutoFilter\u00a0 \u00a0=\u00a0Range(\u201cA2:F2\u201d).AutoFilter<\/p>\n<p>Range(\u201cB2\u201d).Select\u00a0 D\u00f2ng n\u00e0y c\u00f3 th\u1ec3 b\u1ecf\u00a0\u0111i<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_4_Copy_du_lieu_va_paste_sang_bang_chi_tiet_va_hoan_thien_code\"><\/span><strong>B\u01b0\u1edbc 4:\u00a0Copy d\u1eef li\u1ec7u v\u00e0 paste sang b\u1ea3ng chi ti\u1ebft v\u00e0 ho\u00e0n thi\u1ec7n code<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1eeb b\u1ea3ng d\u1eef li\u1ec7u\u00a0\u0111\u00e3\u00a0\u0111\u01b0\u1ee3c l\u1ecdc, ch\u00fang ta copy to\u00e0n b\u1ed9 k\u1ebft qu\u1ea3\u00a0\u0111\u00e3 l\u1ecdc\u00a0\u0111\u01b0\u1ee3c r\u1ed3i d\u00e1n v\u00e0o B\u1ea3ng chi ti\u1ebft\u00a0\u0111\u1ec3 l\u1ea5y k\u1ebft qu\u1ea3.<\/p>\n<p>Code cho b\u01b0\u1edbc n\u00e0y nh\u01b0 sau: (C\u00e1c b\u1ea1n c\u00f3 th\u1ec3\u00a0record macro cho thao t\u00e1c n\u00e0y r\u1ed3i ch\u1ecdn l\u1ecdc code)<\/p>\n<p>V\u00ec b\u1ea3ng chi ti\u1ebft ch\u1ec9 c\u1ea7n l\u1ea5y n\u1ed9i dung T\u00ean h\u00e0ng, s\u1ed1 l\u01b0\u1ee3ng,\u00a0\u0111\u01a1n gi\u00e1, th\u00e0nh ti\u1ec1n n\u00ean n\u1ed9i dung s\u1ebd l\u1ea5y t\u1eeb c\u1ed9t C t\u1edbi c\u1ed9t F, b\u1eaft\u00a0\u0111\u1ea7u t\u1eeb\u00a0\u00f4 C3 t\u1edbi F47 (cu\u1ed1i b\u1ea3ng). Ch\u1ec9 copy nh\u1eefng gi\u00e1 tr\u1ecb xu\u1ea5t hi\u1ec7n sau khi l\u1ecdc<\/p>\n<blockquote>\n<p>ActiveSheet.Range(\u201c$C$3:$F$47\u201d).SpecialCells(xlVisible).Copy<\/p>\n<\/blockquote>\n<p>Paste d\u1eef li\u1ec7u: D\u00e1n v\u00e0o B\u1ea3ng chi ti\u1ebft, b\u1eaft\u00a0\u0111\u1ea7u t\u1eeb\u00a0\u00f4 H6, ch\u1ec9 d\u00e1n d\u1eef li\u1ec7u d\u1ea1ng Value (gi\u00e1 tr\u1ecb)<\/p>\n<blockquote>\n<p>Range(\u201cH6\u201d).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<br \/>\n:=False, Transpose:=False<\/p>\n<\/blockquote>\n<p>Sau khi Paste d\u1eef li\u1ec7u xong, ch\u00fang ta s\u1ebd b\u1ecf l\u1ec7nh Copy v\u00e0 Filter\u00a0\u0111i b\u1eb1ng 2 d\u00f2ng l\u1ec7nh:<\/p>\n<p>Application.CutCopyMode = False\u00a0 \u00a0\u2018H\u1ee7y b\u1ecf ch\u1ebf\u00a0\u0111\u1ed9 Cut Copy trong\u00a0excel (khi b\u1ea1n Cut\/Copy th\u00ec\u00a0Excel s\u1ebd l\u01b0u n\u1ed9i dung\u00a0\u0111\u00f3 trong b\u1ed9 nh\u1edb, v\u00e0 t\u00f4\u00a0\u0111\u01b0\u1eddng vi\u1ec1n nh\u1ea5p nh\u00e1y\u00a0\u1edf n\u1ed9i dung\u00a0\u0111\u00f3. Khi kh\u00f4ng d\u00f9ng\u00a0\u0111\u1ebfn b\u1ea1n c\u00f3 th\u1ec3 b\u1ecf\u00a0\u0111i)<\/p>\n<p>Range(\u201cA2:F2\u201d).AutoFilter\u00a0 \u2018H\u1ee7y b\u1ecf ch\u1ebf\u00a0\u0111\u1ed9 Filter. L\u1eb7p l\u1ea1i thao t\u00e1c n\u00e0y\u00a0\u0111\u1ec3 h\u1ee7y b\u1ecf ch\u1ebf\u00a0\u0111\u1ed9 filter khi kh\u00f4ng d\u00f9ng\u00a0\u0111\u1ebfn n\u1eefa (tr\u1ea3 v\u1ec1 tr\u1ea1ng th\u00e1i ban\u00a0\u0111\u1ea7u khi ch\u01b0a d\u00f9ng Filter)<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_5_Kiem_tra_code_bang_phim_F8\"><\/span><strong>B\u01b0\u1edbc 5: Ki\u1ec3m tra code b\u1eb1ng ph\u00edm F8<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Sau khi ho\u00e0n th\u00e0nh xong code trong VBA, ch\u00fang ta b\u1ea5m n\u00fat F8\u00a0\u0111\u1ec3 ki\u1ec3m tra xem code\u00a0\u0111\u00f3 ho\u1ea1t\u00a0\u0111\u1ed9ng ra sao<\/p>\n<p>N\u1ebfu code ho\u1ea1t\u00a0\u0111\u1ed9ng\u00a0\u0111\u00fang th\u00ec s\u1ebd ra k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12599 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic5.png\" width=\"1387\" height=\"732\" title=\"pic5\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Buoc_6_Gan_macro_vao_su_kien_thay_doi_dieu_kien_o_vung_o_I2_I4\"><\/span><strong>B\u01b0\u1edbc 6: G\u00e1n macro v\u00e0o s\u1ef1 ki\u1ec7n thay\u00a0\u0111\u1ed5i\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u1edf v\u00f9ng \u00f4 I2:I4<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong c\u1eeda s\u1ed5 VBA, b\u1ea1n double click v\u00e0o Sheet1<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12600 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic6.png\" width=\"700\" height=\"296\" title=\"pic6\"><\/p>\n<p>Trong c\u1eeda s\u1ed5 VBA\u00a0l\u00e0m vi\u1ec7c v\u1edbi\u00a0Sheet1, ch\u1ecdn s\u1ef1 ki\u1ec7n Change (thay\u00a0\u0111\u1ed5i n\u1ed9i dung). Khi ch\u00fang ta thay\u00a0\u0111\u1ed5i n\u1ed9i dung trong sheet n\u00e0y th\u00ec s\u1ebd c\u00f3\u00a0\u0111i\u1ec1u g\u00ec x\u1ea3y ra.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12601 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic7.png\" width=\"834\" height=\"416\" title=\"pic7\"><\/p>\n<p>\u1ede\u00a0\u0111\u00e2y ch\u00fang ta quan t\u00e2m t\u1edbi s\u1ef1 thay\u00a0\u0111\u1ed5i d\u1eef li\u1ec7u\u00a0\u1edf\u00a0\u00f4 I2:I4 m\u1edbi l\u00e0m\u00a0\u1ea3nh h\u01b0\u1edfng t\u1edbi b\u00e1o c\u00e1o chi ti\u1ebft. Do\u00a0\u0111\u00f3 ch\u00fang ta ch\u1ec9 x\u00e9t s\u1ef1 thay\u00a0\u0111\u1ed5i\u00a0\u1edf v\u00f9ng n\u00e0y.<\/p>\n<p>Thao t\u00e1c nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12602 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic8.png\" width=\"847\" height=\"108\" title=\"pic8\"><\/p>\n<p>D\u00f2ng l\u1ec7nh \u201c<em>If Not Application.Intersect(Range(\u201cI2:I4\u201d), Range(Target.Address)) Is Nothing Then<\/em>\u201d\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0: N\u1ebfu c\u00f3 s\u1ef1 thay\u00a0\u0111\u1ed5i d\u1eef li\u1ec7u\u00a0\u1edf trong v\u00f9ng I2:I4 x\u1ea3y ra th\u00ec\u2026<\/p>\n<p>Khi thay\u00a0\u0111\u1ed5i d\u1eef li\u1ec7u\u00a0\u1edf v\u00f9ng I2:I4 th\u00ec ch\u00fang ta mu\u1ed1n c\u1eadp nh\u1eadt n\u1ed9i dung c\u1ee7a b\u00e1o c\u00e1o chi ti\u1ebft. Do\u00a0\u0111\u00f3 ch\u00fang ta s\u1ebd g\u1ecdi ra Macro v\u1eeba ho\u00e0n th\u00e0nh\u00a0\u1edf ph\u1ea7n tr\u00ean.<\/p>\n<p><strong>* B\u1ed5 sung:<\/strong><\/p>\n<p>Do m\u1ed7i\u00a0\u0111i\u1ec1u ki\u1ec7n s\u1ebd cho k\u1ebft qu\u1ea3 nhi\u1ec1u \/\u00a0\u00edt kh\u00e1c nhau, do\u00a0\u0111\u00f3\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh r\u00f5 k\u1ebft qu\u1ea3 c\u1ee7a B\u1ea3ng chi ti\u1ebft ch\u1ec9\u00a0\u0111\u00fang v\u1edbi\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u0111\u01b0\u1ee3c ch\u1ecdn, ch\u00fang ta c\u1ea7n l\u00e0m s\u1ea1ch v\u00f9ng B\u1ea3ng chi ti\u1ebft tr\u01b0\u1edbc khi d\u00e1n d\u1eef li\u1ec7u v\u00e0o.<\/p>\n<p>\u0110\u1eb7t d\u00f2ng Code x\u00f3a d\u1eef li\u1ec7u l\u00ean\u00a0\u0111\u1ea7u Macro:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12603 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic9.png\" width=\"877\" height=\"264\" title=\"pic9\"><\/p>\n<p>range(\u201cH6:K100\u201d).ClearContents\u00a0 \u00a0l\u00e0 l\u00e0m s\u1ea1ch d\u1eef li\u1ec7u trong v\u00f9ng H6:K100 (l\u00e0 v\u00f9ng k\u1ebft qu\u1ea3 d\u1eef li\u1ec7u c\u1ee7a b\u1ea3ng chi ti\u1ebft)<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ket_luan\"><\/span>K\u1ebft\u00a0 lu\u1eadn<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Nh\u1eefng n\u1ed9i dung h\u1ecdc\u00a0\u0111\u01b0\u1ee3c qua b\u00e0i n\u00e0y l\u00e0:<\/p>\n<ul>\n<li>C\u00e1ch Record macro v\u00e0 tinh g\u1ecdn code t\u1eeb thao t\u00e1c Record<\/li>\n<li>C\u00e1ch\u00a0\u0111\u1ecdc hi\u1ec3u code trong VBA<\/li>\n<li>C\u00e1ch g\u00e1n Macro v\u00e0o s\u1ef1 ki\u1ec7n x\u1ea3y ra trong Sheet (v\u00ed d\u1ee5 v\u1edbi s\u1ef1 ki\u1ec7n thay\u00a0\u0111\u1ed5i m\u1ed9t s\u1ed1 n\u1ed9i dung trong sheet)<\/li>\n<li>Tr\u00ecnh t\u1ef1 logic c\u1ee7a c\u00e2u l\u1ec7nh trong VBA<\/li>\n<\/ul>\n<p>Trong c\u00f4ng vi\u1ec7c ch\u00fang ta s\u1ebd g\u1eb7p ph\u1ea3i nh\u1eefng vi\u1ec7c n\u00e0y r\u1ea5t nhi\u1ec1u,\u00a0\u0111\u01b0\u1ee3c\u00a0\u1ee9ng d\u1ee5ng nhi\u1ec1u trong th\u1ef1c t\u1ebf.<\/p>\n<p>B\u00e0i vi\u1ebft n\u00e0y s\u1ebd t\u1ea1o ti\u1ec1n\u00a0\u0111\u1ec1 cho c\u00e1c b\u1ea1n l\u00e0m quen v\u1edbi VBA, c\u00e1ch h\u1ecdc VBA d\u1ec5 d\u00e0ng v\u00e0 l\u00e0m quen d\u1ea7n v\u1edbi k\u1ef9 thu\u1eadt VBA gi\u00fap t\u1ef1\u00a0\u0111\u1ed9ng h\u00f3a khi s\u1eed d\u1ee5ng\u00a0Excel.<\/p>\n<p>C\u1ea3m\u00a0\u01a1n c\u00e1c b\u1ea1n\u00a0\u0111\u00e3 theo d\u00f5i.<\/p>\n<blockquote>\n<p>Xem ngay:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-lap-trinh-vba-trong-excel\">kho\u0301a ho\u0323c l\u00e2\u0323p tri\u0300nh VBA trong excel<\/a><\/p>\n<p><a href=\"https:\/\/drive.google.com\/open?id=1CJaceSUGIn9nQebj1ajRxaSCnaZE57Lq\">T\u1ea3i v\u1ec1 t\u00e0i li\u1ec7u k\u00e8m theo b\u00e0i h\u1ecdc<\/a><\/p>\n<\/blockquote>\n<p>\u00a0<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<p>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<p><a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-496.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5667,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5666","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\/5666","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=5666"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5666\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5667"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5666"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5666"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5666"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}