{"id":5547,"date":"2024-12-16T03:30:05","date_gmt":"2024-12-16T03:30:05","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/16\/cach-tim-dong-cuoi-cua-bang-table-trong-excel\/"},"modified":"2024-12-16T03:30:05","modified_gmt":"2024-12-16T03:30:05","slug":"cach-tim-dong-cuoi-cua-bang-table-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/16\/cach-tim-dong-cuoi-cua-bang-table-trong-excel\/","title":{"rendered":"C\u00e1ch t\u00ecm d\u00f2ng cu\u1ed1i c\u1ee7a b\u1ea3ng Table trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p>Ch\u1ee9c n\u0103ng Table Data l\u00e0 m\u1ed9t ch\u1ee9c n\u0103ng r\u1ea5t hay trong\u00a0Excel, tuy nhi\u00ean n\u00f3 c\u0169ng\u00a0\u0111em l\u1ea1i nhi\u1ec1u y\u00eau c\u1ea7u ph\u1ee9c t\u1ea1p h\u01a1n khi s\u1eed d\u1ee5ng. M\u1ed9t trong s\u1ed1\u00a0\u0111\u00f3 l\u00e0 vi\u1ec7c s\u1eed d\u1ee5ng VBA tr\u00ean b\u1ea3ng Table Data.<\/p>\n<p>Trong b\u00e0i h\u1ecdc n\u00e0y ch\u00fang ta c\u00f9ng t\u00ecm hi\u1ec3u vi\u1ec7c s\u1eed d\u1ee5ng VBA\u00a0\u0111\u1ec3 t\u00ecm d\u00f2ng cu\u1ed1i c\u00f9ng c\u00f3 ch\u1ee9a d\u1eef li\u1ec7u trong Table Data.<\/p>\n<p>V\u00ed d\u1ee5: ch\u00fang ta c\u00f3 b\u1ea3ng d\u1eef li\u1ec7u nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13199 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dc3b2ng-cue1bb91i-table-pic01.png\" width=\"598\" height=\"580\" title=\"dc3b2ng-cue1bb91i-table-pic01\"><\/p>\n<p>\u0110\u1ec3 chuy\u1ec3n b\u1ea3ng n\u00e0y v\u1ec1 d\u1ea1ng Table Data th\u00ec ch\u00fang ta ch\u1ecdn to\u00e0n b\u1ed9 b\u1ea3ng, sau\u00a0\u0111\u00f3 ch\u1ecdn<strong>\u00a0Insert<\/strong>&gt;<strong>Table<\/strong>&gt; Trong b\u1ea3ng Create Table th\u00ec b\u1ea1n ch\u00fa\u00a0\u00fd\u00a0\u0111\u00e1nh th\u00eam d\u1ea5u ch\u1ecdn v\u00e0o\u00a0\u00f4 <em>My table has headers<\/em> (B\u1ea3ng c\u00f3 bao g\u1ed3m ti\u00eau\u00a0\u0111\u1ec1) =&gt; B\u1ea5m\u00a0<strong>OK<\/strong><\/p>\n<p>B\u00e2y gi\u1edd ch\u00fang ta c\u00f9ng t\u00ecm hi\u1ec3u m\u1ed9t v\u00e0i\u00a0\u1ee9ng d\u1ee5ng VBA t\u00ecm d\u00f2ng cu\u1ed1i\u00a0\u0111\u1ec3 xem n\u00f3 ho\u1ea1t\u00a0\u0111\u1ed9ng th\u1ebf n\u00e0o trong m\u00f4i tr\u01b0\u1eddng VBA<\/p>\n<p><strong>1. Ph\u01b0\u01a1ng ph\u00e1p End(xlUp) th\u00f4ng th\u01b0\u1eddng<\/strong><\/p>\n<p>\u0110\u00e2y l\u00e0 ph\u01b0\u01a1ng ph\u00e1p kh\u00e1 th\u00f4ng d\u1ee5ng trong VBA\u00a0\u0111\u1ec3 t\u00ecm d\u00f2ng cu\u1ed1i c\u1ee7a\u00a0 1 v\u00f9ng d\u1eef li\u1ec7u.<\/p>\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-198.gif\" title=\"vba101_92019-nho-198\"><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-482.gif\" title=\"ex101_92019-nho-482\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>C\u00e1ch vi\u1ebft th\u01b0\u1eddng g\u1eb7p l\u00e0:<\/p>\n<blockquote>\n<p>T\u00ean Sheet.Cells(Rows.Count, S\u1ed1 c\u1ed9t).End(xlUp).Row<\/p>\n<\/blockquote>\n<ul>\n<li>T\u00ean Sheet: B\u1ea1n mu\u1ed1n l\u00e0m vi\u1ec7c t\u1ea1i sheet n\u00e0o th\u00ec s\u1eed d\u1ee5ng t\u00ean sheet\u00a0\u0111\u00f3<\/li>\n<li>Rows.count:\u00a0\u0110\u1ebfm s\u1ed1 h\u00e0ng<\/li>\n<li>S\u1ed1 c\u1ed9t: B\u1ea1n mu\u1ed1n\u00a0\u0111\u1ebfm trong c\u1ed9t n\u00e0o th\u00ec s\u1eed d\u1ee5ng s\u1ed1 c\u1ed9t\u00a0\u0111\u00f3<\/li>\n<li>End(xlUp).Row : t\u00ednh t\u1eeb to\u00e0n b\u1ed9 b\u1ea3ng s\u1ed1 d\u00f2ng c\u1ee7a b\u1ea3ng t\u00ednh t\u00ednh ng\u01b0\u1ee3c l\u00ean ph\u00eda tr\u00ean t\u1edbi d\u00f2ng g\u1ea7n nh\u1ea5t c\u00f3 ch\u1ee9a d\u1eef li\u1ec7u<\/li>\n<\/ul>\n<p>Ki\u1ec3m tra c\u00e1ch vi\u1ebft n\u00e0y v\u1edbi Table Data nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13200 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dc3b2ng-cue1bb91i-table-pic02.png\" width=\"1230\" height=\"420\" title=\"dc3b2ng-cue1bb91i-table-pic02\"><\/p>\n<p>Ch\u00fang ta c\u00f3\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 l\u00e0 d\u00f2ng th\u1ee9 13<\/p>\n<p><em><strong>Nh\u01b0ng khi x\u00f3a b\u1edbt 1 v\u00e0i d\u1eef li\u1ec7u trong b\u1ea3ng Table, v\u00ed d\u1ee5 nh\u01b0 x\u00f3a d\u1eef li\u1ec7u t\u1ea1i\u00a0d\u00f2ng 11, 12, 13\u00a0th\u00ec k\u1ebft qu\u1ea3 v\u1eabn ra 13<\/strong><\/em><\/p>\n<p>=&gt; Vi\u1ec7c ghi nh\u1eadn d\u00f2ng cu\u1ed1i theo c\u00e1ch n\u00e0y\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 t\u1edbi d\u00f2ng cu\u1ed1i c\u1ee7a b\u1ea3ng Table\u00a0\u0111ang\u00a0\u00e1p d\u1ee5ng, kh\u00f4ng ph\u1ee5 thu\u1ed9c ph\u00eda trong n\u00f3 c\u00f3 d\u1eef li\u1ec7u hay kh\u00f4ng<\/p>\n<p><strong>2. Ph\u01b0\u01a1ng ph\u00e1p End(xlUp) \/ End(xlDown) d\u00f9ng ri\u00eang v\u1edbi Table<\/strong><\/p>\n<p>\u0110\u1eb7t t\u00ean Table v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta l\u00e0 TableTest<\/p>\n<p>Trong c\u00e1ch vi\u1ebft d\u00f9ng ri\u00eang v\u1edbi Table, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1ch vi\u1ebft:<\/p>\n<blockquote>\n<p>T\u00ean sheet.Range(\u201cT\u00ean table\u201d).End(xlUp).Row<\/p>\n<p>T\u00ean sheet.Range(\u201cT\u00ean table\u201d).End(xlDown).Row<\/p>\n<\/blockquote>\n<p>Ch\u00fang ta c\u00f9ng ki\u1ec3m tra k\u1ebft qu\u1ea3 c\u1ee7a t\u1eebng tr\u01b0\u1eddng h\u1ee3p:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13201 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dc3b2ng-cue1bb91i-table-pic03.png\" width=\"678\" height=\"472\" title=\"dc3b2ng-cue1bb91i-table-pic03\"><\/p>\n<p>C\u00e1ch s\u1eed d\u1ee5ng End(xlUp) cho ch\u00fang ta k\u1ebft qu\u1ea3 = 2<\/p>\n<p>C\u00e1ch s\u1eed d\u1ee5ng End(xlDown) cho ch\u00fang ta k\u1ebft qu\u1ea3 = 10<\/p>\n<p>Nh\u01b0 v\u1eady ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y c\u00e1ch s\u1eed d\u1ee5ng xlDown s\u1ebd cho ch\u00fang ta\u00a0\u0111\u00fang d\u00f2ng cu\u1ed1i c\u1ea7n t\u00ecm trong Table<\/p>\n<p>Ngay c\u1ea3 khi c\u00f3 d\u1eef li\u1ec7u b\u00ean ngo\u00e0i table th\u00ec v\u1eabn cho ra k\u1ebft qu\u1ea3 l\u00e0 d\u00f2ng cu\u1ed1i trong Table, b\u1edfi c\u00e2u l\u1ec7nh tr\u00ean ch\u1ec9 x\u00e9t trong v\u00f9ng b\u1ea3ng Table<\/p>\n<p><em><strong>Tuy nhi\u00ean n\u1ebfu d\u00f2ng cu\u1ed1i c\u00f3 ch\u1ee9a d\u1eef li\u1ec7u b\u1ecb\u00a0\u1ea9n\u00a0\u0111i (ch\u1ee9c n\u0103ng Hide Row) th\u00ec c\u00e2u l\u1ec7nh tr\u00ean s\u1ebd kh\u00f4ng t\u00ednh v\u1edbi c\u00e1c d\u00f2ng b\u1ecb\u00a0\u1ea9n, v\u00e0 n\u1ebfu c\u00f3 d\u00f2ng tr\u1ed1ng n\u1eb1m xen gi\u1eefa c\u00e1c n\u1ed9i dung trong b\u1ea3ng th\u00ec c\u00e1ch n\u00e0y ch\u1ec9 t\u00ecm\u00a0\u0111\u1ebfn v\u1ecb tr\u00ed d\u00f2ng tr\u1ed1ng xen gi\u1eefa\u00a0\u0111\u00f3 m\u00e0 th\u00f4i.<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13202 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dc3b2ng-cue1bb91i-table-pic04.png\" width=\"698\" height=\"408\" title=\"dc3b2ng-cue1bb91i-table-pic04\"><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13204 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dc3b2ng-cue1bb91i-table-pic05.png\" width=\"700\" height=\"444\" title=\"dc3b2ng-cue1bb91i-table-pic05\"><\/p>\n<p><strong>3. Ph\u01b0\u01a1ng ph\u00e1p Range.Find<\/strong><\/p>\n<p>\u0110\u00e2y l\u00e0 ph\u01b0\u01a1ng ph\u00e1p\u00a0\u0111\u01b0\u1ee3c nh\u1eefng ng\u01b0\u1eddi c\u00f3 nhi\u1ec1u kinh nghi\u1ec7m s\u1eed d\u1ee5ng VBA\u00a0\u01b0a th\u00edch, b\u1edfi n\u00f3 kh\u1eafc ph\u1ee5c\u00a0\u0111\u01b0\u1ee3c h\u1ea7u h\u1ebft c\u00e1c nh\u01b0\u1ee3c\u00a0\u0111i\u1ec3m c\u1ee7a c\u00e1c ph\u01b0\u01a1ng ph\u00e1p t\u00ecm d\u00f2ng cu\u1ed1i kh\u00e1c:<\/p>\n<ul>\n<li>C\u00f3 d\u00f2ng tr\u1ed1ng xen k\u1ebd\u00a0\u1edf trong b\u1ea3ng<\/li>\n<li>D\u00f2ng cu\u1ed1i c\u00f3 ch\u1ee9a c\u00f4ng th\u1ee9c nh\u01b0ng k\u1ebft qu\u1ea3 l\u00e0 r\u1ed7ng<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13205 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dc3b2ng-cue1bb91i-table-pic06.png\" width=\"1334\" height=\"484\" title=\"dc3b2ng-cue1bb91i-table-pic06\"><\/p>\n<p>Trong h\u00ecnh tr\u00ean,\u00a0\u1edf 2\u00a0\u00f4 C4 v\u00e0 C5\u00a0\u0111\u00e3\u00a0\u00e1p d\u1ee5ng c\u00e1ch s\u1eed d\u1ee5ng t\u00ecm d\u00f2ng cu\u1ed1i theo ph\u01b0\u01a1ng ph\u00e1p Range.Find<\/p>\n<p>C4: T\u00ecm gi\u00e1 tr\u1ecb kh\u00e1c r\u1ed7ng trong b\u1ea3ng TableTest,\u00a0\u0111\u1ed1i t\u01b0\u1ee3ng t\u00ecm ki\u1ebfm l\u00e0\u00a0Value<\/p>\n<p>C5: T\u00ecm gi\u00e1 tr\u1ecb kh\u00e1c r\u1ed7ng trong b\u1ea3ng TableTest,\u00a0\u0111\u1ed1i t\u01b0\u1ee3ng t\u00ecm ki\u1ebfm l\u00e0 Formulas, t\u00ednh c\u1ea3\u00a0\u00f4 ch\u1ee9a c\u00f4ng th\u1ee9c m\u00e0 b\u1eb1ng r\u1ed7ng<\/p>\n<p>M\u1eb7c d\u00f9 ph\u01b0\u01a1ng ph\u00e1p n\u00e0y kh\u00e1 d\u00e0i nh\u01b0ng n\u00f3 gi\u00fap ch\u00fang ta kh\u1eafc ph\u1ee5c\u00a0\u0111\u01b0\u1ee3c nhi\u1ec1u nh\u01b0\u1ee3c\u00a0\u0111i\u1ec3m hay g\u1eb7p, gi\u00fap l\u00e0m vi\u1ec7c tr\u00ean Table Data m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 h\u01a1n.<\/p>\n<p>T\u00f9y t\u1eebng tr\u01b0\u1eddng h\u1ee3p m\u00e0 ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng\u00a0ph\u01b0\u01a1ng ph\u00e1p ph\u00f9 h\u1ee3p\u00a0\u0111\u1ec3\u00a0\u0111\u1ea1t\u00a0\u0111\u01b0\u1ee3c m\u1ee5c\u00a0\u0111\u00edch.<\/p>\n<p>T\u00e0i li\u1ec7u tham kh\u1ea3o:\u00a0https:\/\/fastexcel.wordpress.com\/<\/p>\n<p>C\u00f2n v\u00f4 v\u00e0n \u1ee9ng d\u1ee5ng n\u1eefa m\u00e0 khi th\u1ef1c hi\u1ec7n \u1edf VBA s\u1ebd gi\u00fap b\u1ea1n ti\u1ebft ki\u1ec7m th\u1eddi gian r\u1ea5t nhi\u1ec1u, qua \u0111\u00f3 t\u0103ng hi\u1ec7u qu\u1ea3 c\u00f4ng vi\u1ec7c v\u00e0 hi\u1ec7u su\u1ea5t l\u00e0m vi\u1ec7c.<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 h\u1ecdc VBA m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, c\u00f3 h\u1ec7 th\u1ed1ng, b\u1ea1n h\u00e3y tham gia kho\u00e1 h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2OAUBOg\">VBA101 \u2013 VBA c\u01a1 b\u1ea3n d\u00e0nh cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u<\/a>\u00a0c\u1ee7a h\u1ec7 th\u1ed1ng H\u1ecdc Excel Online. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 nhi\u1ec1u \u01b0u \u0111\u00e3i khi b\u1ea1n \u0111\u0103ng k\u00fd kh\u00f3a h\u1ecdc n\u00e0y. Chi ti\u1ebft xem t\u1ea1i:<\/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<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-198.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5548,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5547","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\/5547","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=5547"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5547\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5548"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}