Delete row loạt sheet vba

Trong một file Excel có thể tạo nhiều Sheet làm việc, nhưng bạn chỉ cần dùng một hay một số Sheet và không cần các Sheet còn lại. Với một vài Sheet thì có thể dùng cách xóa thủ công nhưng số lượng vài chục hay vài trăm Sheet thì nên sử dụng câu lệnh để thực hiện công việc này sẽ tiết kiệm thời gian và công sức đáng kể.

Xem bài học trước

>> VBA Excel Cấp Tốc Bài 1 - Lọc Dữ Liệu Nhiều Điều Kiện Với Advanced Filter

>> VBA Excel Cấp Tốc Bài 2 - In Bảng Cửu Chương

>> VBA Excel Cấp Tốc Bài 3 - In Ấn Nhanh Hàng Loạt

Video hướng dẫn

Mở Visual Basic

Mở Visual Basic để code những câu lệnh thực hiện việc xóa các Sheet không Active: Developer -> Visual Basic ->  chuột phải vào Sheet 1 -> Insert -> Module.

Delete row loạt sheet vba

Code chương trình Xóa Sheet không Active

Cấu trúc của một chương trình vẫn là Sub sau đó đến tên chương trình enter phần kết trúc chương trình sẽ tự động sinh ra.

Delete row loạt sheet vba

  • Thực hiện khai báo một biến có tên là biến Worksheet với dòng lệnh:
  • Sử dụng vòng lập For để quét tất cả các Sheet xem Sheet nào không phải là Sheet Active thì nó sẽ xóa đi, lệnh này với cú pháp là: For Each ws In ThisWorkbook.Sheets -> enter và Next để kết thúc vòng lặp.
  • Tiếp tục If ws.Name <> ThisWorkbook.Activesheet.Name Then có nghĩa là nếu quét đến cái Sheet mà khác cái Sheet mình đang lựa chọn thao tác thì sẽ xóa đi -> enterEnd If để kết thúc Điều kiện If.
  • Bên trong hàm If viết câu lệnh nếu là khác thì xóa: Ws.Delete

Delete row loạt sheet vba

Khi kích vào Run để chạy chương trình và sẽ hiện thông báo bạn thực sự có muốn xóa không bạn kích chọn Delete để xóa và thông báo cứ hiện lênh khi xóa mỗi Sheet.

Delete row loạt sheet vba

Với số lượng rất nhiều Sheet nên không thể tích Delete mãi được thì mình sử dụng dòng lệnh: Application.DisplayAlerts = False và dòng lệnh: Application.DisplayAlerts = True hiện thông báo khi thực hiện lại xóa Sheet để tránh nhưng nguy hiểm cho người sử dụng sau.

Delete row loạt sheet vba

Tạo Nút Nhấn Button

Phần này tùy vào nhu cầu hay chương trình của bạn có nên tạo Button hay không. Mình sẽ hướng dẫn cho các bạn cần tạo: Developer -> Insert -> chọn hình giữ chuột và kéo -> tích vào Macro mà bạn muốn gán cho Button -> ok.

Delete row loạt sheet vba

Đoạn code VBA của chương trình xóa Sheets không Active:

Sub Xoa_sheet()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
        ws.Delete
    End If
Next
Application.DisplayAlerts = True
End Sub

Xem bài tiếp theo

>> VBA Excel Cấp Tốc Bài 5 - Copy File Từ Một Thư Mục Sang Thư Mục Khác

  • #1

Option Explicit
Sub xoadongcodieukien()
    Dim dc&, i&
    dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    For i = dc To 2 Step -1
        If (Sheet1.Range("A" & i) = Sheet1.Range("C1").Value) Then
            Sheet1.Rows(i).Delete
        End If
    Next i
End Sub

em có code xoá dòng theo điều kiện như trên nhưng nếu dữ liệu lớn dữ liệu load rất lâu, vậy có cách nào để xoá dòng nhanh hơn trong vba excel không ạ?

  • #2

Mỗi lần xóa dòng thì Excel sẽ phải đẩy dòng ở dưới lên lấp chỗ trống, nếu dòng cần xóa nằm rải rác hoặc xóa từng dòng sẽ rất lâu. Để khắc phục, bạn sử dụng cột phụ để đánh dấu dòng cần xóa rồi sort cho dòng đó xuống dưới, chỉ xóa một lần là xong.

Option Explicit
Sub xoadongcodieukien()
Dim dc&, i&, arr1(), arr2(), x, LastCol&
dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Redim arr1(1 to dc - 1, 1 to 1)
Redim arr2(1 to dc - 1, 1 to 1)
arr1 = sheet1.range("A2:A" & dc).value
x = Sheet1.Range("C1").Value
For i = 1 To dc - 1
If arr1(i,1) =  x Then arr(i,2)=1
Next i
LastCol=sheet1.range("A1").end(xltoright).column
Sheet1.Range("A1").offset(1,LastCol).resize(dc-1)=arr2
'Sort bảng dữ liệu và xóa ở đây
End Sub

Do mình viết trên điện thoại nên bạn tự hoàn thiện code nhé.

  • #3

Bạn tham khảo topic này:

Bạn cần đăng nhập để thấy link

Ở trên, bạn NhanSu đã dùng mảng để xử lý nhằm tăng tốc độ, thay vì xử lý trực tiếp trên Excel (cách ban đầu của bạn).

  • #4

Mỗi lần xóa dòng thì Excel sẽ phải đẩy dòng ở dưới lên lấp chỗ trống, nếu dòng cần xóa nằm rải rác hoặc xóa từng dòng sẽ rất lâu. Để khắc phục, bạn sử dụng cột phụ để đánh dấu dòng cần xóa rồi sort cho dòng đó xuống dưới, chỉ xóa một lần là xong.

Option Explicit
Sub xoadongcodieukien()
Dim dc&, i&, arr1(), arr2(), x, LastCol&
dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Redim arr1(1 to dc - 1, 1 to 1)
Redim arr2(1 to dc - 1, 1 to 1)
arr1 = sheet1.range("A2:A" & dc).value
x = Sheet1.Range("C1").Value
For i = 1 To dc - 1
If arr1(i,1) =  x Then arr(i,2)=1
Next i
LastCol=sheet1.range("A1").end(xltoright).column
Sheet1.Range("A1").offset(1,LastCol).resize(dc-1)=arr2
'Sort bảng dữ liệu và xóa ở đây
End Sub

Do mình viết trên điện thoại nên bạn tự hoàn thiện code nhé.

cảm ơn @NhanSu rất nhiều mình áp dụng thử

  • #5

Option Explicit
Sub xoadongcodieukien()
Dim dc&, i&, arr1(), arr2(), x, LastCol&
dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Redim arr1(1 to dc - 1, 1 to 1)
Redim arr2(1 to dc - 1, 1 to 1)
arr1 = sheet1.range("A2:A" & dc).value
x = Sheet1.Range("C1").Value
For i = 1 To dc - 1
If arr1(i,1) =  x Then arr(i,2)=1
Next i
LastCol=sheet1.range("A1").end(xltoright).column
Sheet1.Range("A1").offset(1,LastCol).resize(dc-1)=arr2
'Sort bảng dữ liệu và xóa ở đây
End Sub

Đề bài : Tìm trên cột A nếu giá trị nào bằng ô C1 thì delete dòng đó đi.

Bạn cần đăng nhập để thấy hình ảnh

Em nghĩ dòng code số 10 của anh sai:

If arr1(i,1) =  x Then arr(i,2)=1

Em nghĩ code đúng sẽ là:

If arr1(i, 1) = x Then arr2(i, 1) = 1

Khi đó kết quả chạy code sẽ là:

Bạn cần đăng nhập để thấy hình ảnh

Không biết như thế có đúng không.
Em không hiểu chỗ này:

'Sort bảng dữ liệu và xóa ở đây

Có phải ý tưởng chỗ này là dùng filter rồi xóa một loạt không ạ?

Bạn cần đăng nhập để thấy hình ảnh

Hay là sort như thế này anh nhỉ:

Bạn cần đăng nhập để thấy hình ảnh

Bạn cần đăng nhập để thấy hình ảnh

Mong được anh chỉ bảo thêm ạ. Em mới học VBA 1 tuần nên nhiều cái còn chưa biết.

thanhphong

Guest

  • #6

Em là thanhphong đến từ diễn đàn THVBA.
Em xin phép được sử dụng lại code của bác Smod @NhanSu .
Ở code này em cố gắng giảm thiểu khai báo mảng, không dùng sort. Em sẽ xóa trực tiếp một lần.

Sub xoadongcodieukien()
Dim dc&, i&, arr1(), x, LastCol&
Dim rng As Range
Dim cnt As Long

dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
ReDim arr1(1 To dc - 1, 1 To 1)
ReDim arr2(1 To dc - 1, 1 To 1)
arr1 = Sheet1.Range("A2:A" & dc).Value
x = Sheet1.Range("C1").Value
cnt = 0
With ThisWorkbook.Sheets(1)
    For i = 1 To dc - 1
        If arr1(i, 1) = x Then
            cnt = cnt + 1
            If cnt = 1 Then
                Set rng = .Rows(i + 1)
            Else
                Set rng = Union(rng, .Rows(i + 1))
            End If
        End If
    Next i
    If cnt > 0 Then
        rng.Delete
    End If
End With

End Sub

Các dòng code tô sáng là nơi em sửa code.

  • #7

Thấy bạn trên dùng phương thức Union. Mình xin gửi 1 cách khác cho mọi người tham khảo:

Sub ABC()
    Dim Rng As Range, i&, iRow&, DK$, Arr()
    Application.ScreenUpdating = False
    With Sheet1
        Arr = .Range("A2:A" & .Range("A" & Rows.Count).End(3).Row).Value
        DK = .Range("C1").Value
        For i = 1 To UBound(Arr, 1)
            If Arr(i, 1) = DK Then
                If Rng Is Nothing Then
                    Set Rng = .Range("A" & i + 1)
                Else
                    Set Rng = Union(Rng, .Range("A" & i + 1))
                End If
            End If
        Next i
    If Not Rng Is Nothing Then Rng.EntireRow.Delete
    End With
    Application.ScreenUpdating = True
End Sub

  • #8

Các bạn thử code xóa vài nghìn dòng không liên tục với dữ liệu khoảng vài trăm nghìn dòng sẽ thấy ý nghĩa của việc sort. Với dữ liệu như vậy, không sort thì thời gian không dưới 15ph, còn vừa sort vừa xóa chỉ 2s. Có điều cần chú ý khi sort thì không nên để công thức vì có thể sau sort thì tham chiếu trong công thức thay đổi nên kết quả sai. Đúng là code của mình viết nhầm và cần bỏ lọc trước khi sort.

  • #9

Đã xác nhận. Anh @NhanSu nói đúng. Thử lên tầm khoảng 100,000 dòng dữ liệu. Quay đắm đuối luôn.

Delete row loạt sheet vba

  • #10

Topic từ 2020 mà giờ đào xới lên sôi nổi vậy .
Nếu không hợp cells và định dạng gì, chỉ đơn thuần là dữ liệu, sao không dùng ADO duyệt điều kiện ghi ra mảng .
Clear vùng dữ liệu .
Ghi mảng trả về sheet . Vậy có phải đỡ nhọc không mọi người .

  • #11

Mọi người cho mình hỏi cách

- Xóa dữ liệu ở một hoặc nhiều ô khi đóng hoặc mở Excel.
- Chỉ cho save mà không cho save as.

Xin cám ơn mọi người.

Delete row loạt sheet vba

  • #12

Nếu file save là file viết code thì :