Xóa Cột Trong Excel Bằng Vba / Top 7 # Xem Nhiều Nhất & Mới Nhất 2/2023 # Top View | Hoisinhvienqnam.edu.vn

Cách Ẩn Cột, Ẩn Hàng Trong Excel Bằng Vba

Việc ẩn cột, ẩn hàng trong Excel được ứng dụng trong nhiều trường hợp:

Bạn muốn trình bày một số nội dung, trong đó những cột / hàng chứa nội dung không cần thiết sẽ được ẩn đi thay vì xóa. Vì khi ẩn đi thì có thể mở ẩn là sử dụng bình thường, còn xóa thì sẽ mất luôn những dữ liệu đã xóa, gây ảnh hưởng tới những nội dung khác.

Trong một số hàm tính toán có phân biệt những giá trị ẩn, do đó việc ẩn/ mở ẩn sẽ ảnh hưởng tới 1 số hàm và kết quả mong muốn.

Khi copy dữ liệu trong VBA, chúng ta cần ẩn những nội dung không cần thiết đi để lấy được kết quả mong muốn

Cách ẩn Cột trong VBA

1. Ẩn cột đã được xác định cụ thể

Những cột đã được xác định cụ thể là những cột có thể gọi tên một cách trực tiếp.

Câu lệnh như sau:

Tên sheet.Vùng chứa những cột cần ẩn.EntireColumn.Hidden = True

Gọi tên vùng chứa cột cần ẩn, sau đó sử dụng ” EntireColumn ” để chỉ định tới các cột trong vùng đó.

Ẩn cột A và cột C trong Sheet1

View the code on Gist.

Trong ví dụ trên, vì cùng thực hiện trong Sheet1 do đó chúng ta có thể đặt With Sheet1…. End With để viết Code cho gọn hơn

Để ẩn các cột liền nhau thì ra có thể gom chung các vùng đó, ví dụ như ẩn cột từ A đến D (gồm các cột A, B, C, D) như sau:

Sheet1.Range(“A1:D1”).EntireColumn.Hidden = True

2. Ẩn cột không xác định cụ thể

Những cột không xác định cụ thể là cột được xác định dựa theo Range động.

Ví dụ:

Ẩn cột từ cột C tới cột cuối cùng có chứa dữ liệu trong bảng sau:

View the code on Gist.

Cách bỏ ẩn cột trong VBA

Để mở ẩn cột, chúng ta sẽ chọn thuộc tính True bằng thuộc tính False trong câu lệnh ẩn cột

Câu lệnh như sau:

Tên sheet.Vùng chứa những cột cần ẩn.EntireColumn.Hidden = False

Ví dụ:

Để mở ẩn cột A, B, C, D đã ẩn ở trên, chúng ta dùng câu lệnh:

Sheet1.Range(“A1:D1”).EntireColumn.Hidden = False

Cách Ẩn / Bỏ ẩn Hàng trong VBA

Tương tự với cách ẩn cột, chúng ta thực hiện việc ẩn hàng bằng cách thay nội dung ‘EntireColumn’ thành ‘EntireRow’

Câu lệnh ẩn hàng như sau:

Tên sheet.Vùng chứa những hàng cần ẩn.EntireRow.Hidden = True

Câu lệnh bỏ ẩn hàng như sau:

Tên sheet.Vùng chứa những hàng cần ẩn.EntireRow.Hidden = False

Ví dụ:

Ẩn dòng từ dòng 2 tới dòng 4 trong Sheet1:

Sheet1.Range(A2:A4).EntireRow.Hidden = True

Bỏ ẩn dòng 2 tới dòng 4 trong Sheet 1:

Sheet1.Range(A2:A4).EntireRow.Hidden = False

Ẩn từ dòng 5 tới dòng cuối cùng có chứa dữ liệu trong bảng trên:

View the code on Gist.

Tải về file mẫu tại: http://bit.ly/2snJ3ak

Đến đây bạn đã có thể bắt đầu tìm hiểu những điều thú vị của VBA được rồi. VBA giúp ích rất nhiều trong công việc: giúp bạn tăng tốc trong quá trình xử lý trên Excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như Word, Power Point, Outlook … giúp biến file Excel của bạn trở thành 1 phần mềm chuyên nghiệp …

Hướng Dẫn Xóa Tất Cả Text Box Trong Word Bằng Vba

Rất đơn giản phải không nào? Cách này rất hiệu quả đối với số ít text box. Vậy nếu bạn muốn xóa nhiều text box cùng lúc thì sao?

Xóa tất cả TEXT BOX trong Word bằng VBA

Bước 1: Nhấn ALT + F11 để mở cửa sổ VBA trong Word.

Cách Xóa Các Hàng Trống Trong Excel Với Vba

Có nhiều cách để xóa các hàng trống trong trang tính Excel, nhưng cách nhanh chóng nhất là sử dụng macro trong Excel. Bất kể lý do xuất hiện các hàng trống trong Excel là gì, tốt nhất là bạn nên xóa chúng. Hầu hết các tính năng của Excel diễn giải một hàng trống là phần cuối của một dải dữ liệu; do đó, các tính năng đó sẽ không hoạt động như mong đợi nếu bạn để các hàng trống trong tập dữ liệu của mình.

Trong bài viết này, chúng ta sẽ khám phá cách tạo một macro giúp bạn nhanh chóng xóa các hàng trống trong Excel.

Mã VBA xóa hàng trống trong Excel

Đầu tiên, chúng ta hãy xem mã hiển thị bên dưới. Đừng quá lo lắng vì đoạn code này không phức tạp như vẻ bề ngoài. Logic rất đơn giản. Sau khi khai báo và thiết lập một vài biến, vòng lặp For sẽ kiểm tra mọi hàng trong phạm vi đã chọn để tìm các hàng trống. Khi nó tìm thấy bất kỳ hàng trống nào, code này sẽ xóa hàng và dịch chuyển các hàng phía dưới lên.

Mã VBA xóa hàng trống trong Excel

Sub DeleteBlankRows()'Delete blank rows in a selected range.'Choose appropriate delete statement in For loop.'Entire Row deletes entire rows.'Delete deletes partial rows.Dim rng As RangeDim selectedRng As RangeDim iRowCount As IntegerDim iForCount As Integer'Error handling; for good measure.On Error Resume NextSet selectedRng = Application.Selection'Type:=8 argument specifies a Range object; input value must be a range.Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8)Application.ScreenUpdating = False'Count of rows in selected range used as For stop value.'WorksheetFunction.CountA counts the number of cells that are NOT empty.iRowCount = selectedRng.Rows.CountFor iForCount = iRowCount To 1 Step -1 If Application.WorksheetFunction.CountA(selectedRng.Rows(iForCount)) = 0 Then 'Delete entire row. selectedRng.Rows(iForCount).EntireRow.Delete 'Delete partial row. 'selectedRnd.Rows(iForCount).Delete End IfNext Application.ScreenUpdating = TrueEnd Sub

Một số lệnh có thể cần giải thích một chút.

Đầu tiên là hàm InputBox:

Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8)

Có thể bạn đã quen thuộc với hàm InputBox như một công cụ để thu thập dữ liệu từ người dùng. Trong trường hợp này, người dùng phải chỉ định một phạm vi vì đối số, Type: = 8, chỉ định rằng đầu vào là một đối tượng Range.

Câu lệnh iRowCount = selectRng.Rows.Count trả về số hàng trong phạm vi đầu vào (từ hộp nhập).

Vòng lặp For sử dụng biến này (trừ 1) làm giá trị dừng của nó. Câu lệnh đầu tiên trong vòng lặp For là câu lệnh If.

Hàm CountA đếm số ô trong hàng hiện tại không trống. Khi số ô không trống là 0, câu lệnh tiếp theo sẽ xóa toàn bộ hàng.

Đoạn code để xóa dòng trống trong excel này bao gồm hai câu lệnh xóa: một câu lệnh xóa toàn bộ hàng và một câu lệnh xóa một phần các hàng trong phạm vi đã chọn.

Hoặc để đơn giản hơn, bạn có thể tạo hai macro: một macro xóa toàn bộ hàng và một macro chỉ xóa một phần hàng.

Bây giờ bạn đã biết cách hoạt động của macro, hãy thêm nó vào sổ làm việc của mình.

Đầu tiên, mở VBA bằng cách nhấn Alt + F11.

Trong Project Explorer, truy cập module ThisWorkbook và nhập đoạn code bên trên vào.

Tiếp theo, quay lại Excel và lưu nó dưới dạng tệp hỗ trợ macro như sau:

Bấm vào tab File.

Chọn Save as từ ngăn bên trái.

Từ menu thả xuống File Type (bên dưới điều khiển Name), chọn Sổ làm việc Hỗ trợ Macro của Excel (* .xlsm).

Nhấp vào để Save.

Như vậy, bạn đã thêm macro thành công vào trang tính của mình.

Sử dụng macro để xóa các hàng trống trong Excel

Nhấp vào tab Developer.

Trong nhóm Code, bấm Macro.

Trong hộp thoại kết quả, chọn ThisWorkbook.DeleteBlankRows và nhấp vào Run.

Bạn sẽ thấy các hộp nhắc hiển thị như hình bên dưới. Nếu bạn chọn phạm vi trước khi chạy macro, hộp nhập sẽ mặc định thành phạm vi đã chọn. Tuy nhiên, bạn có thể đánh dấu phạm vi hoặc nhập phạm vi theo cách thủ công cũng như sử dụng hộp nhập liệu. Bạn cũng có thể nhập tên dải ô hoặc tên đối tượng Table.

Nhấp vào OK và bạn đã hoàn tất. Các hàng trống đã biến mất.

Đầu tiên, bạn không thể hoàn tác việc xóa. Lời khuyên tốt nhất của tôi là luôn lưu bất kỳ sổ làm việc nào trước khi xóa dữ liệu. Bằng cách đó, nếu bạn mắc lỗi, bạn vẫn ổn.

Thứ hai, nếu bạn đang làm việc với một tập dữ liệu lớn, việc chọn phạm vi có thể khó khăn. May mắn thay, macro có thể xử lý tên phạm vi hoặc tên đối tượng Bảng.

Bạn có thể nhận thấy rằng còn một số ô trống trong bản ghi. Macro sẽ không xóa các bản các ô trống này, đoạn mã trên chỉ xóa các hàng trong phạm vi được chọn.

Nếu bạn thường xuyên sử dụng macro, hãy cân nhắc thêm nó vào QAT của bạn

Cách thêm macro vào thanh công cụ QAT

Bạn có thể dễ dàng để thêm một hoặc hai macro vào Office QAT để nhanh chóng thực hiện các tác vụ thường xuyên lập lại. Các bước thực hiện như sau:

Nhấp vào menu thả xuống QAT và chọn More Commands.

Từ menu thả xuống Commands From, chọn Macro.

Ứng dụng của bạn sẽ hiển thị các macro đang có trong danh sách kết quả. Chọn macro bạn muốn thêm vào QAT và nhấp vào nút Add

Nhấn OK để thêm macro vào QAT.

Sau đó, bạn có thể tùy chỉnh macro trên thanh Office QAT của mình để hiển thị đẹp mắt hơn. Bạn có thể thay đổi biểu tượng macro với các bước như sau:

Lặp lại các bước 1 và 2 ở trên để truy cập các tùy chọn giao diện QAT.

Chọn macro trong danh sách Customize Quick Access Toolbar.

Nhấp vào Edit.

Trong hộp thoại hiện ra, chọn một biểu tượng mới. Bạn cũng có thể thay đổi tên.

Nhấn OK hai lần để xem biểu tượng mới trong QAT.

Hướng Dẫn Cách Sắp Xếp Dữ Liệu Trong Excel Bằng Vba

Sắp xếp là một việc thường xuyên được thực hiện khi thao tác với dữ liệu trong VBA. Lợi ích của việc sắp xếp giúp cho chúng ta quản lý dữ liệu tốt hơn, dễ dàng tra cứu, kiểm tra hơn. Trong bài học này chúng ta cùng tìm hiểu một số cách sử dụng lệnh Sort trong VBA:

Ví dụ 1: Sắp xếp không bao gồm tiêu đề

Cho bảng dữ liệu sau, hãy sắp xếp thứ tự nội dung trong bảng theo dữ liệu ở cột D

Câu lệnh sắp xếp như sau:

View the code on Gist.

Trong câu lệnh này, bảng tính giới hạn từ ô A2 tới dòng cuối của cột G (toàn bộ bảng tính từ cột A tới G, từ dòng 2 tới dòng cuối có chứa dữ liệu).

Nội dung sắp xếp là nội dung tại cột D, tính từ ô D2

Thứ tự sắp xếp là thứ tự tăng dần (nếu sắp xếp giảm dần thì dùng xlDescending thay cho xlAscending)

Thứ tự sắp xếp có thể biểu diễn dưới dạng số:

xlAscending là số 1

xlDescending là số 2

Ví dụ 2: Sắp xếp có bao gồm tiêu đề

Câu lệnh sắp xếp với dữ liệu trên có bao gồm tiêu đề như sau:

View the code on Gist.

Những nội dung cần chú ý:

Vùng bắt đầu của bảng dữ liệu: Bao gồm cả dòng tiêu đề (trong ví dụ này bắt đầu từ dòng thứ 1)

Header = xlYes để thể hiện vùng dữ liệu này có tiêu đề, Header = xlNo để thể hiện không có tiêu đề. Tuy nhiên nếu vùng dữ liệu được chọn không có chứa tiêu đề thì không cần xét tiêu chí này (như ví dụ 1)

Dữ liệu sắp xếp ở cột D vẫn bắt đầu từ ô D1 hoặc D2 đều được

Ví dụ 3: Sắp xếp thứ tự trong tất cả các sheet

Nếu yêu cầu sắp xếp là bắt buộc và áp dụng trên tất cả các sheet thì chúng ta có thể áp dụng nội dung này để có thể sắp xếp dữ liệu một cách nhanh chóng chỉ với 1 câu lệnh:

View the code on Gist.

Diễn giải cách thực hiện:

Sử dụng vòng lặp để chạy qua các đối tượng là WorkSheet nằm trong Workbook đang làm việc

Tại mỗi Sheet đi qua thực hiện lệnh lọc dữ liệu với vùng dữ liệu được xác định: cột A đến cột G, từ dòng 2 tới dòng cuối có chứa dữ liệu, sắp xếp tại cột D từ ô D2, sắp xếp theo thứ tự tăng dần (1 = xlAscending)

Như vậy chúng ta đã có thể nắm được cách thức thực hiện lệnh sắp xếp trong VBA rồi phải không nào. Câu lệnh sắp xếp khá đơn giản và đem lại hiệu quả cao, giúp chúng ta có thể quản lý dữ liệu tốt hơn trong khi làm việc trên Excel.

Tải về file mẫu tại địa chỉ: http://bit.ly/2B8IP5K