Home » VBA Excel » Excel VBA: Xóa dòng/xóa dòng trống

Excel VBA: Xóa dòng/xóa dòng trống

viết bởi Lê Quốc Thái
21 lượt xem

Dưới đây là 6 phương pháp sẽ xóa các dòng theo tiêu chí được chọn. Nếu bạn biết phạm vi cần xoá, bạn có thể thay thế “Selection” bằng Range(). Cần lưu ý là các cách dưới đây ít hiệu quả nhất do liên quan đến sử dụng vòng lặp (loops). Bởi vì nó chỉ xóa một dòngng tại một thời điểm!
Trong một số ví dụ, tôi tắt tính năng Calculation và Screenupdating.
Lý do tắt Calculation là trong trường hợp phạm vi các dòng bị xoá có chứa rất nhiều công thức, nếu bật Calculation Excel có thể cần phải tính toán lại mỗi khi một dòng bị xóa sẽ làm chậm quá trình thực thi code vba. Screenupdating = False cũng sẽ tăng tốc độ code vba, vì Excel sẽ không làm mới lại màn hình mỗi khi nó xoá một dòng. Subs: DeleteBlankRows1, DeleteBlankRows3 và cả hai sự kiện Worksheet_Change hơi khác nhau khi nó kiểm tra xem nếu toàn bộ dòng là trống.

Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.
'We use Long in case they have over 32,767 rows selected.
Dim i As Long 
	'We turn off calculation and screenupdating to speed up the macro.
	With Application
		.Calculation = xlCalculationManual
		.ScreenUpdating = False
	'We work backwards because we are deleting rows.
	For i = Selection.Rows.Count To 1 Step -1
		If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
			Selection.Rows(i).EntireRow.Delete
		End If
	Next i
		.Calculation = xlCalculationAutomatic
		.ScreenUpdating = True
 	End With
End Sub

Sub DeleteBlankRows2()

'Deletes the entire row within the selection if _
 'some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Sub DeleteBlankRows3()

'Deletes the entire row within the selection if _
'the ENTIRE row contains no data.
Dim Rw As Range
If WorksheetFunction.CountA(Selection) = 0 Then
   MsgBox "No data found", vbOKOnly, "LeQuocThai.Com"
   Exit Sub
End If
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    Selection.SpecialCells(xlCellTypeBlanks).Select
        For Each Rw In Selection.Rows
            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
                Selection.EntireRow.Delete
            End If
        Next Rw
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Sub MoveBlankRowsToBottom()

'Assumes the list has a heading
	With Selection

		.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _

			Header:=xlYes, OrderCustom:=1, MatchCase:=False, _

			Orientation:=xlTopToBottom

	End With 

End Sub

Sub DeleteRowsBasedOnCriteria()

'Assumes the list has a heading.

   	With ActiveSheet

             If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter

                    .Range("A1").AutoFilter Field:=1, Criteria1:="Delete"

                    .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _

			(xlCellTypeVisible).EntireRow.Delete

     		.AutoFilterMode = False

	End With

End Sub

Sub DeleteRowsWithSpecifiedData()

'Looks in Column D and requires Column IV to be clean

	Columns(4).EntireColumn.Insert

	With Range("D1:D" & ActiveSheet.UsedRange.Rows.Count)

 			.FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Not Needed"",NA()))"

			.Value = .Value

			On Error Resume Next

			.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

	End With

	On Error GoTo 0

	Columns(4).EntireColumn.Delete

End Sub

Để sử dụng bất kỳ hoặc tất cả các mã trên:

Mở Excel.
Nhấn Alt + F11 để mở VBE (Visual Basic Editor).
Chọn menu Insert>Module.
Sao chép mã và dán nó vào Module mới.
Nhấn Alt+Q để trở về dạng xem thường của Excel.
Nhấn Alt+F8 rồi chọn tên macro rồi bấm Run. Hoặc chọn Options và gán phím tắt.


Loại bỏ dòng trống tự động

Các đoạn code ở trên sẽ làm việc tốt để loại bỏ các dòng trống từ một danh sách vùng chọn sẵn có.
Hai ví dụ dưới đây sẽ loại bỏ các dòng trống khi thoả điều kiện. Cả hai đoạn code nên được đặt trong module worksheet và sẽ thực thi mỗi khi một ô thay đổi trên bảng tính.
Trong cả hai đoạn code, chúng ta thiết lập Application.EnableEvents = False là cần thiết trong sự kiện như thế này, nếu không sự kiện sẽ được kích hoạt một lần nữa một khi mã thực hiện mà lần lượt sẽ kích hoạt lại sự kiện giống như kiểu vòng lặp…

Bạn sẽ không nghi ngờ khi nhận thấy GoTo SelectionCode được dùng nếu số lượng các ô trong vùng lựa chọn vượt quá một. Lý do là sẽ có lỗi xảy ra nếu mã đạt đến Target mục tiêu như Target mà Target này đề cập đến một ô duy nhất.
Ví dụ thứ hai sử dụng phương pháp Sort chứ không phải là EntireRow.Delete và là phương pháp khuyến nghị sử dụng nếu có thể. Điều này sẽ xảy ra khi bất kỳ dòng trống được đặt ở dưới cùng của vùng và chúng sẽ bị xoá.
Việc sử dụng từ khóa Me là một thói quen tốt để tiện khi làm việc trong Worksheet and Workbook module.


Private Sub Worksheet_Change(ByVal Target As Range)

'Deletes blank rows as they occur.

	'Prevent endless loops

	Application.EnableEvents = False

	'They have more than one cell selected

	If Target.Cells.Count > 1 Then GoTo SelectionCode

		If WorksheetFunction.CountA(Target.EntireRow) = 0 Then

			Target.EntireRow.Delete

		End If

	Application.EnableEvents = True

	'Our code will only enter here if the selection is more than one cell.

	Exit Sub

SelectionCode:

	If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then

		Selection.EntireRow.Delete

	End If

	Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Sorts blank rows to the bottom as they occur

	'Prevents endless loops

	Application.EnableEvents = False

	'They have more than one cell selected

		If Target.Cells.Count > 1 Then GoTo SelectionCode

			If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then

				Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _

					Header:=xlYes, OrderCustom:=1, MatchCase:=False, _

					Orientation:=xlTopToBottom

		End If

	Application.EnableEvents = True

Exit Sub 'Our code will only enter here if the selection is _
'more than one cell.

SelectionCode:

	If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then

		Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _

			Header:=xlYes, OrderCustom:=1, MatchCase:=False, _

			Orientation:=xlTopToBottom

	End If

	Application.EnableEvents = True

End Sub

Để sử dụng một trong các mã trên:


Mở Excel.
Nhấn Alt + F11 để mở VBE (Visual Basic Editor).
Chọn menu Insert>Module.
Sao chép mã và dán nó vào Module mới.
Nhấn Alt+Q để trở về dạng xem thường của Excel.
Nhấn Alt+F8 rồi chọn tên macro rồi bấm Run. Hoặc chọn Options và gán phím tắt.
Trong số tất cả các ví dụ trên sử dụng Excel AutoFilters và Sort là các phương pháp nhanh nhất mà tôi
biết. Nếu bạn biết cách nhanh hơn, xin vui lòng để lại bình luận bên dưới để tôi có thể học hỏi thêm.

Bài viết liên quan

Viết ý kiến của bạn

LeQuocThai.Com - Chuyên trang Excel | Sử dụng cookies để tăng trãi nghiệm người dùng. Đồng ý Xem thêm

viVI
en_USEN viVI