File 500 Sheet nhỏ (giống nhau), cần Code xóa những Sheet (có B7 and C7=0)

Dauthivan · 24/2/12

File của em khoảng 500 Sheet nhỏ như nhau, tuy nhiên có rất nhiều File mà cột tổng cộng (tại các dòng C7 và B7=0), em muốn xóa các Sheet này đi vì không dùng đến chúng.
Em xin gửi file đính kèm (minh họa những Sheet cần xóa bôi màu vàng ở Tab)

Hoàng Trọng Nghĩa · 24/2/12

Bạn chỉ cần làm như sau:

Mã:

Sub XoaSheet()
    Dim ShXoa As Worksheet
    With Application
        .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
        For Each ShXoa In ThisWorkbook.Worksheets
            [COLOR=#ff0000][B]If ShXoa.Name <> "SheetKhongXoa" Then[/B][/COLOR]
                If ShXoa.Range("B7").Value = 0 And _
                    ShXoa.Range("C7").Value = 0 Then _
                ShXoa.Delete
            [COLOR=#ff0000][B]End If[/B][/COLOR]
        Next
        .EnableEvents = True: .DisplayAlerts = True: .ScreenUpdating = True
    End With
End Sub

Lưu ý với dòng màu đỏ, nếu Sheet nào bạn không muốn xóa thì bạn cứ đặt vào và thêm And là xong.

Dauthivan · 27/2/12

Phiền bác Nghĩa hộ em: Bây giờ em muốn Sheet nào đồng thời tất cả các ô trong vùng E5:H10 đều bằng 0 thì xóa Sheet đó đi thì cú pháp viết sao hả bác?

PHP:

Sub XoaSheet()
    Dim ShXoa As Worksheet
    With Application
        .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
        For Each ShXoa In ThisWorkbook.Worksheets
            If ShXoa.Range("E5:H10").Value = 0 Then
                ShXoa.Delete
            End If
        Next
        .EnableEvents = True: .DisplayAlerts = True: .ScreenUpdating = True
    End With
End Sub

ndu96081631 · 27/2/12

Dauthivan đã viết:
Phiền bác Nghĩa hộ em: Bây giờ em muốn Sheet nào đồng thời tất cả các ô trong vùng E5:H10 đều bằng 0 thì xóa Sheet đó đi thì cú pháp viết sao hả bác?
]

Hỏi lại: Vùng E5:H10 nếu có cell rổng thì có tính là 0 hay không?

Dauthivan · 27/2/12

Có thày ah. Tức là toàn bộ vùng đó rỗng hoặc bằng 0 thì xóa Sheet đó đi.

kyo · 27/2/12

Dauthivan đã viết:
Có thày ah. Tức là toàn bộ vùng đó rỗng hoặc bằng 0 thì xóa Sheet đó đi.

Theo mình thì là vậy, nhưng mà nếu như các sheet của bạn đều bằng 0 hết thì sẽ gây ra lỗi vì nó xóa hết sạch sheet đi ấy

PHP:

Sub XoaSheet()
    Dim ShXoa As Worksheet
    With Application
        .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
        For Each ShXoa In ThisWorkbook.Worksheets
            If WorksheetFunction.CountIf(ShXoa.Range("E1:H10"), "*") = 0 Then
                ShXoa.Delete
            End If
        Next
        .EnableEvents = True: .DisplayAlerts = True: .ScreenUpdating = True
    End With
End Sub

ndu96081631 · 27/2/12

kyo đã viết:
Theo mình thì là vậy, nhưng mà nếu như các sheet của bạn đều bằng 0 hết thì sẽ gây ra lỗi vì nó xóa hết sạch sheet đi ấy

PHP:

Sub XoaSheet() Dim ShXoa As Worksheet With Application .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False For Each ShXoa In ThisWorkbook.Worksheets If WorksheetFunction.CountIf(ShXoa.Range("E1:H10"), "*") = 0 Then ShXoa.Delete End If Next .EnableEvents = True: .DisplayAlerts = True: .ScreenUpdating = True End With End Sub

Điều kiện COUNTIF là "*" e rằng không đúng
Tôi nghĩ là vầy:

PHP:

For Each ShXoa In ThisWorkbook.Worksheets
  tmp1 = WorksheetFunction.CountIf(ShXoa.Range("E5:H10"), "")
  tmp2 = WorksheetFunction.CountIf(ShXoa.Range("E5:H10"), 0)
  If tmp1 + tmp2 = ShXoa.Range("E5:H10").Count Then ShXoa.Delete
Next

Với tmp1 và tmp2 là 2 biến Long

kyo · 27/2/12

ndu96081631 đã viết:
Điều kiện COUNTIF là "*" e rằng không đúng
Tôi nghĩ là vầy:

PHP:

For Each ShXoa In ThisWorkbook.Worksheets tmp1 = WorksheetFunction.CountIf(ShXoa.Range("E5:H10"), "") tmp2 = WorksheetFunction.CountIf(ShXoa.Range("E5:H10"), 0) If tmp1 + tmp2 = ShXoa.Range("E5:H10").Count Then ShXoa.Delete Next

Với tmp1 và tmp2 là 2 biến Long

kyo thấy vẫn đúng mà? kyo đã test với trường hợp số 0 và cell rỗng, chỉ có cell " " (cell có một ký tự trắng) thì mới không xóa được thôi, và kyo thấy là cả 2 code đều như nhau. Kyo không biết là có trường hợp nào ngoại lệ không, xin chú ndu chỉ với

nguyenmanhnam · 27/2/12

Em chưa rõ lắm về một số trường hợp trong VBA, mong các sư phụ dạy bảo cho:

1. Em đang lơ tơ mơ về cái WorksheetFunction, chỉ nhớ có lần đọc qua bài của thày Ndu nói rằng hạn chế dùng cái này, vậy nó là cái gì, trường hợp nào bắt buộc phải có nó?

2. Mấy hôm nay em đang tìm đọc về sự khác nhau giữa Clean và Trim trong VBA, nhưng chưa rõ chúng khác nhau cái gì vì em thấy cả 2 cùng cắt đi ký tự không cần thiết?

kyo · 27/2/12

nguyenmanhnam đã viết:
Em chưa rõ lắm về một số trường hợp trong VBA, mong các sư phụ dạy bảo cho:

1. Em đang lơ tơ mơ về cái WorksheetFunction, chỉ nhớ có lần đọc qua bài của thày Ndu nói rằng hạn chế dùng cái này, vậy nó là cái gì, trường hợp nào bắt buộc phải có nó?

2. Mấy hôm nay em đang tìm đọc về sự khác nhau giữa Clean và Trim trong VBA, nhưng chưa rõ chúng khác nhau cái gì vì em thấy cả 2 cùng cắt đi ký tự không cần thiết?

Theo mình thì
1. WorksheetFunction có thể hiểu là gồm một số hàm được thiết kế sẵn để bổ trợ thêm (giống với hàm Excel) nhằm khi VBA không có hàm nào thay thế được và chỉ dùng khi thực sự VBA không có (không nên dùng tràn lan). Chẳng hạn như, bạn nên dùng hàm Sum của WorksheetFunction thay vì tự viết cho mình một hàm Sum.

2. Sự khác biệt giữa Clean và Trim cũng khá dễ thấy, bạn có thể xem trong file Help
- Trim: The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160.

- Clean: The Clean function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).

ndu96081631 · 27/2/12

kyo đã viết:
kyo thấy vẫn đúng mà? kyo đã test với trường hợp số 0 và cell rỗng, chỉ có cell " " (cell có một ký tự trắng) thì mới không xóa được thôi, và kyo thấy là cả 2 code đều như nhau. Kyo không biết là có trường hợp nào ngoại lệ không, xin chú ndu chỉ với

Rổng nó có nhiều loại lắm! Giả sử trường hợp rổng do công thức trả về thì sao? (ví dụ tại E5 ta gõ ="" )

ptm0412 · 27/2/12

Vậy tại sao không phải là WorksheetFunction.Sum(ShXoa.Range("E5:H10")) = 0?

Chỉ trừ trường hợp có cả số âm và số dương và tổng âm bằng đúng tổng dương mà thôi.

nguyenmanhnam · 27/2/12

Xin anh Kyo nói rõ hơn là tại sao lại hạn chế dùng, phải chăng nó ảnh hưởng đến tốc độ xử lý chậm hơn chăng?

ndu96081631 · 27/2/12

ptm0412 đã viết:
Vậy tại sao không phải là WorksheetFunction.Sum(ShXoa.Range("E5:H10")) = 0?

Chỉ trừ trường hợp có cả số âm và số dương và tổng âm bằng đúng tổng dương mà thôi.

Còn trường hợp 1 cell nào đó bị lỗi nữa sư phụ à, dẫn đến hàm SUM bị lỗi luôn (trong khi COUNTIF không bị vụ này)
-----------------

nguyenmanhnam đã viết:
Xin anh Kyo nói rõ hơn là tại sao lại hạn chế dùng, phải chăng nó ảnh hưởng đến tốc độ xử lý chậm hơn chăng?

Đó là 1 nguyên nhân!
Ngoài ra bạn nghĩ đi: Nếu dùng WorksheetFunction, vậy thôi ta gõ hàm trực tiếp lên bảng tính cho rồi, cần gì phải code (xài WorksheetFunction cũng giống như trường hợp vừa xài code lại vừa xài công thức)... Đương nhiên trừ những trường hợp mà ta đã cân nhắc thật kỹ (giống như trường hợp xóa sheet ở trên, dùng COUNIF là gọn nhât)

ptm0412 · 27/2/12

Túm lại là từ thuở biết dùng Excel đến giờ, mình chưa bao giờ để xảy ra tình trạng phải xoá sheet hàng loạt thế nầy. Một workbook tối đa 10-15 sheet (đó là file dự toán 15 hạng mục của bên KH chuyển sang, bản thân tạo ra thì không tới). 500 sheets, cha mẹ ơi!

Tạo sheet cho cố vô rồi file ì ạch chạy không nổi, đến lúc muốn xoá cũng không xoá nổi luôn. Chi cho khổ vậy?

Với lại tạo sheet thì ưa dùng cách dễ, chứ mấy ai dùng cách khó, toàn là nhấn chuột phải copy sheet, 1 cách nhân bản name rác và object rác cực kỳ nhanh.

Lê Duy Thương · 27/2/12

từ trước tới giờ tôi dùng excel nhiều lắm cũng chỉ có 40 sheet trong 1 workbook . đó là file mà tôi đã nhờ sư phụ ptm0412, anh tuấn, và bác sealand viết code. cứ tưởng mình là vô định dùng nhiều sheet. nhưng hôm nay thấy mình chưa bằng 1/10
ec. ec.

Dauthivan · 28/2/12

Nếu bảng Excel do mình tạo ra thì cũng không nhiều Sheet như vậy, nhưng cái này là do chương trình kế toán kết xuất ra (quy trình:chương trình kế toán >>xuất ra PDF>>xuất ra Excel). Hôm qua em kết xuất ra file 913 bảng, mỗi bảng nó chỉ có khoảng 10 dòng thôi trong đó thực ra mình chỉ dùng có khoảng 20 bảng, cái này vì chương trình không tự động nhặt ra được nên đành phải nghĩ cách xóa bằng VBA.

Không dấu gì các thày, bọn em làm ở Ngân hàng CP thương mại tư nhân áp lực lớn, yêu cầu làm không được tính nhầm, hôm nào hầu như cũng liên tục từ 7h sáng đến 7 h tối, lắm khi đầu óc còn không đủ minh mẫn để nghĩ cách giải quyết vấn đề, chỉ có mấy hôm nghỉ Tết vừa rồi em có tự học được một chút cách dùng VBA tuy nhiên do không có thời gian thực hành nên kỹ năng vẫn rất kém, bài nào không biết thì Post lên nhờ các thày giúp đỡ

Xin cảm ơn các thày rất nhiều, em hy vọng sẽ được cách thày tiếp tục chỉ bảo, giúp đỡ

nguyenmanhnam · 2/3/12

Thưa các thày em chưa rõ lắm tại sao WorksheetFunction.CountIf(ShXoa.Range("E5:H10"), "") thì lại cần có WorksheetFunction trong khi đó một số hàm như Mid, Replace... thì lại không cần WorksheetFunction đằng trước.

PHP:

Option Explicit
Function SepString(Text As String, Pos As Long)
  On Error GoTo NextStp
  Text = Mid(Text, InStr(Text, "(") + 1, Len(Text))
  Text = Replace(Text, ")", "")
  Text = Replace(Text, " ", "")
  Text = Replace(Text, ";", " ")
  Text = Replace(Text, "/", " ")
  SepString = Split(Text, " ")(Pos - 1)
  If SepString = "" Then SepString = 0
  Exit Function
NextStp:
  SepString = ""
End Function

ndu96081631 · 2/3/12

nguyenmanhnam đã viết:
Thưa các thày em chưa rõ lắm tại sao WorksheetFunction.CountIf(ShXoa.Range("E5:H10"), "") thì lại cần cóWorksheetFunctiontrong khi đó một số hàm như Mid, Replace... thì lại không cầnWorksheetFunction đằng trước.

PHP:

Option Explicit Function SepString(Text As String, Pos As Long) On Error GoTo NextStp Text = Mid(Text, InStr(Text, "(") + 1, Len(Text)) Text = Replace(Text, ")", "") Text = Replace(Text, " ", "") Text = Replace(Text, ";", " ") Text = Replace(Text, "/", " ") SepString = Split(Text, " ")(Pos - 1) If SepString = "" Then SepString = 0 Exit Function NextStp: SepString = "" End Function

Vì MID, REPLACE là hàm có sẵn của VBA (vừa có trong VBA, vừa có trên bảng tính)... còn COUNTIF thì không phải (chỉ có trên bảng tính)
Vậy nên hàm nào dùng với WorksheetFunction thì ta biết chắc hàm ấy thuộc bảng tính (có thể gõ trên bảng tính)... Còn hàm nào không dùng WorksheetFunction thì ta biết chắc hàm ấy là hàm có sẵn trong VBA
Rất nhiều hàm thuộc dạng này (có trên bảng tính nhưng không có trong VBA) như: SUM, MAX, MIN, VLOOKUP, SUMIF... vân vân

File 500 Sheet nhỏ (giống nhau), cần Code xóa những Sheet (có B7 and C7=0)

Thành viên tiêu biểu

File đính kèm

Chuyên gia GPE

File đính kèm

Thành viên tiêu biểu

Huyền thoại GPE

Thành viên tiêu biểu

Nguyễn Khắc Duy

Huyền thoại GPE

Nguyễn Khắc Duy

Thành viên tiêu biểu

Nguyễn Khắc Duy

Huyền thoại GPE

Bad Excel Member

Thành viên tiêu biểu

Huyền thoại GPE

Bad Excel Member

Cạo lấy gì gội (Dịch quá không gội được)

Thành viên tiêu biểu

Thành viên tiêu biểu

Huyền thoại GPE

Đếm ngược thời gian