Code xóa toàn bộ các dòng và cột ẩn (ẩn = Filter) của tất cả các sheet trong 1 file (1 người xem)

AnhThu-1976 · 17/5/18

Thầy/cô, anh/chị cho em hỏi có Code nào xóa toàn bộ các dòng và cột ẩn của tất cả các sheet trong 1 file?
Em cảm ơn!

vanthinh3101 · 17/5/18

AnhThu-1976 đã viết:
Thầy/cô, anh/chị cho em hỏi có Code nào xóa toàn bộ các dòng và cột ẩn của tất cả các sheet trong 1 file?
Em cảm ơn!

Bạn thử code này xem.

Mã:

Sub GPE()
    Dim Rng As Range, Ws As Worksheet
    
    For Each Ws In ThisWorkbook.Sheets
        For Each Rng In Ws.UsedRange
            If Rng.EntireRow.Hidden Then
                Rng.EntireRow.Delete
            ElseIf Rng.EntireColumn.Hidden Then
                Rng.EntireColumn.Delete
            End If
        Next Rng
    Next Ws
    MsgBox "Done", vbInformation, "GPE"
End Sub

ndu96081631 · 17/5/18

vanthinh3101 đã viết:

Bạn thử code này xem.

Mã:

Sub GPE()
    Dim Rng As Range, Ws As Worksheet
   
    For Each Ws In ThisWorkbook.Sheets
        For Each Rng In Ws.UsedRange
            If Rng.EntireRow.Hidden Then
                Rng.EntireRow.Delete
            ElseIf Rng.EntireColumn.Hidden Then
                Rng.EntireColumn.Delete
            End If
        Next Rng
    Next Ws
    MsgBox "Done", vbInformation, "GPE"
End Sub

For Each Rng In Ws.UsedRange như vậy là vòng lập phải duyệt quá nhiều. Ví dụ vùng UsedRange có 5 cột 10 dòng, vị chi tổng số ô là 50 ===> Bạn phải duyệt 50 lần trong vòng lập
Giải pháp: Duyệt theo số cột, cột nào bị ẩn thì xóa. Tiếp theo duyệt theo số dòng, dòng nào bị ẩn thì xóa. Theo như ví dụ nêu ở trên, với vùng UsedRange có 5 cột 10 dòng thì ta chỉ cần 5 lần lập theo cột + 10 lần lập theo dòng = tổng cổng 15 lần lập
(Lưu ý: duyệt ngược từ tổng số cột hoặc dòng đến 1 Step -1)

AnhThu-1976 · 17/5/18

Ứng dụng code ở bài # 2 cho file có khoảng 30 sheet thì thấy code chạy hoài không ra kết quả!
Để giới hạn lại số dòng hay cột ẩn khi chạy code thì số dòng trong các sheet không quá 5.000 dòng và số cột thì không quá cột CZ

ndu96081631 · 17/5/18

AnhThu-1976 đã viết:
Ứng dụng code ở bài # 2 cho file có khoảng 30 sheet thì thấy code chạy hoài không ra kết quả!
Để giới hạn lại số dòng hay cột ẩn khi chạy code thì số dòng trong các sheet không quá 5.000 dòng và số cột thì không quá cột CZ

Dùng UsedRange thì tự Excel nó biết đâu là giới hạn rồi đó bạn

ndu96081631 · 17/5/18

AnhThu-1976 đã viết:
Thầy/cô, anh/chị cho em hỏi có Code nào xóa toàn bộ các dòng và cột ẩn của tất cả các sheet trong 1 file?
Em cảm ơn!

Không bàn đến code, tôi có 1 thắc mắc rằng: Lý do gì bạn phải xóa những cells ẩn thay vì cho hiện nó ra? Việc xóa dòng, cột trong bảng tính đôi khi ảnh hướng nghiêm trọng đến các liên kết công thức nên bạn phải thật cẩn thận. Bởi macro đã chạy là không có chuyện Undo được đâu

Ba Tê · 17/5/18

AnhThu-1976 đã viết:
Ứng dụng code ở bài # 2 cho file có khoảng 30 sheet thì thấy code chạy hoài không ra kết quả!
Để giới hạn lại số dòng hay cột ẩn khi chạy code thì số dòng trong các sheet không quá 5.000 dòng và số cột thì không quá cột CZ

Viết theo ý bài #3, bạn chạy thử xem:

PHP:

Public Sub GPE()
Dim Ws As Worksheet, Col As Long, Rws As Long, I As Long
For Each Ws In ThisWorkbook.Worksheets
    With Ws
        Col = .UsedRange.Columns.Count
        Rws = .UsedRange.Rows.Count
        For I = Col To 1 Step -1
            If .Cells(1, I).EntireColumn.Hidden = True Then .Cells(1, I).EntireColumn.Delete
        Next I
        For I = Rws To 1 Step -1
            If .Cells(I, 1).EntireRow.Hidden = True Then .Cells(I, 1).EntireRow.Delete
        Next I
    End With
Next Ws
End Sub

AnhThu-1976 · 17/5/18

ndu96081631 đã viết:
Lý do gì bạn phải xóa những cells ẩn thay vì cho hiện nó ra?

Em hay kết xuất 1 số sheet gởi cho khách hàng, các sheet này em ẩn đi những gì không cần thiết (tính toán phụ), nhưng khi họ mở file ra và unhide thì họ thắc mắc!
Và cũng có nhiều người thắc mắc tại sao không xóa nó đi mà che lại!...

ndu96081631 đã viết:
Việc xóa dòng, cột trong bảng tính đôi khi ảnh hưởng nghiêm trọng đến các liên kết công thức nên bạn phải thật cẩn thận.

Vì các dòng và cột ẩn là tính toán phụ trợ (ví dụ kiểm tra số liệu trên bản chính có đúng không) nên nó sẽ không ảnh hưởng gì hết ạ.

AnhThu-1976 · 17/5/18

Em chạy code nó báo lỗi
"Run time error '1004
Delete methor of range class failed"
và code dưới bị tô màu vàng!

Ba Tê đã viết:
.Cells(1, I).EntireColumn.Delete

Nhờ Thầy xem lại giúp em, em cảm ơn!

Ba Tê · 17/5/18

AnhThu-1976 đã viết:
Em chạy code nó báo lỗi
"Run time error '1004
Delete methor of range class failed"
và code dưới bị tô màu vàng!

Nhờ Thầy xem lại giúp em, em cảm ơn!

Bạn đưa cái file chạy lỗi lên đây xem.

AnhThu-1976 · 17/5/18

Ba Tê đã viết:
Bạn đưa cái file chạy lỗi lên đây xem.

Em gởi file đính kèm

VetMini · 17/5/18

AnhThu-1976 đã viết:
Em hay kết xuất 1 số sheet gởi cho khách hàng, các sheet này em ẩn đi những gì không cần thiết (tính toán phụ), nhưng khi họ mở file ra và unhide thì họ thắc mắc!
Và cũng có nhiều người thắc mắc tại sao không xóa nó đi mà che lại!...

Vì các dòng và cột ẩn là tính toán phụ trợ (ví dụ kiểm tra số liệu trên bản chính có đúng không) nên nó sẽ không ảnh hưởng gì hết ạ.

Người ta phải giữ bản gốc lại làm chứng. Chỉ copy phần không ẩn sang file khác thôi.

Ba Tê · 17/5/18

AnhThu-1976 đã viết:
Em gởi file đính kèm

Bạn đảo thứ tự xóa dòng trước xem sao, không biết trong mấy cái "ẩn" của bạn có gì.

PHP:

        For I = Rws To 1 Step -1
            If .Cells(I, 1).EntireRow.Hidden = True Then .Cells(I, 1).EntireRow.Delete
        Next I
        For I = Col To 1 Step -1
            If .Cells(1, I).EntireColumn.Hidden = True Then .Cells(1, I).EntireColumn.Delete
        Next I

ndu96081631 · 17/5/18

AnhThu-1976 đã viết:
Em gởi file đính kèm

File đang ở chế độ filter chứ đâu phải ẩn gì chứ. Thêm cái ShowAllData vào:

Mã:

Public Sub GPE()
Dim Ws As Worksheet, Col As Long, Rws As Long, I As Long
For Each Ws In ThisWorkbook.Worksheets
    With Ws
      If .FilterMode = True Then .ShowAllData
        Col = .UsedRange.Columns.Count
        Rws = .UsedRange.Rows.Count
        For I = Col To 1 Step -1
            If .Cells(1, I).EntireColumn.Hidden = True Then .Cells(1, I).EntireColumn.Delete
        Next I
        For I = Rws To 1 Step -1
            If .Cells(I, 1).EntireRow.Hidden = True Then .Cells(I, 1).EntireRow.Delete
        Next I
    End With
Next Ws
End Sub

Ba Tê · 17/5/18

ndu96081631 đã viết:

File đang ở chế độ filter chứ đâu phải ẩn gì chứ. Thêm cái ShowAllData vào:

Mã:

Public Sub GPE()
Dim Ws As Worksheet, Col As Long, Rws As Long, I As Long
For Each Ws In ThisWorkbook.Worksheets
    With Ws
      If .FilterMode = True Then .ShowAllData
        Col = .UsedRange.Columns.Count
        Rws = .UsedRange.Rows.Count
        For I = Col To 1 Step -1
            If .Cells(1, I).EntireColumn.Hidden = True Then .Cells(1, I).EntireColumn.Delete
        Next I
        For I = Rws To 1 Step -1
            If .Cells(I, 1).EntireRow.Hidden = True Then .Cells(I, 1).EntireRow.Delete
        Next I
    End With
Next Ws
End Sub

Híc! Cắm đầu vào code mà không xem file.
Yêu cầu xóa dòng ẩn "phá sản", chẳng có dòng nào bị Hide.

ndu96081631 · 17/5/18

Ba Tê đã viết:
Yêu cầu Ẩn dòng "phá sản", chẳng có dòng nào bị Hide.

Hi... Hi... em thấy nhưng cứ.. kệ. Nếu là yêu cầu "XÓA NHỮNG DÒNG ẨN DO FILTER" thì đó lại là bài toán hoàn toàn khác + code khác

AnhThu-1976 · 17/5/18

ndu96081631 đã viết:
Nếu là yêu cầu "XÓA NHỮNG DÒNG ẨN DO FILTER" thì đó lại là bài toán hoàn toàn khác + code khác

Vì em nghỉ Filter để ẩn dòng thì nó # ẩn dòng, nên em mới hỏi
Xin lỗi vì đã làm mất thời gian của các thầy & anh
Vậy cho em hỏi có cách nào xóa các dòng hoặc cột mà đã ẩn = Filter không ạ!

ndu96081631 · 17/5/18

AnhThu-1976 đã viết:
Vì em nghỉ Filter để ẩn dòng thì nó # ẩn dòng, nên em mới hỏi

thì đúng là nó khác mà

AnhThu-1976 đã viết:
Vậy cho em hỏi có cách nào xóa các dòng hoặc cột mà đã ẩn = Filter không ạ!

Code theo nguyên tắc:
- Xét xem sheet có ở chế độ filter không, nếu không có thì thoát
- Set vùng đang hiện vào 1 biến (rngFilter chẳng hạn)
- ShowAll data
- Ẩn vùng rngFilter
- Xóa phần còn lại trên bảng tính
- Cho hiện tất cả
Xong
(Tức là đảo ngược quá trình: vùng đang hiện thì ta cho ẩn và vùng đang ẩn thì cho hiện ra. Xong xóa thằng đang hiện)

HieuCD · 17/5/18

AnhThu-1976 đã viết:
Vì em nghỉ Filter để ẩn dòng thì nó # ẩn dòng, nên em mới hỏi
Xin lỗi vì đã làm mất thời gian của các thầy & anh
Vậy cho em hỏi có cách nào xóa các dòng hoặc cột mà đã ẩn = Filter không ạ!

Chạy code

Mã:

Sub GPE()
  Dim Ws As Worksheet, i As Long
  Dim cRng As Range, rRng As Range
  For Each Ws In ThisWorkbook.Worksheets
    With Ws.UsedRange
      For i = 1 To .Columns.Count
        If .Cells(1, i).EntireColumn.Hidden = True Then
          If cRng Is Nothing Then Set cRng = .Cells(1, i) Else Set cRng = Union(cRng, .Cells(1, i))
        End If
      Next i
      For i = 1 To .Rows.Count
        If .Cells(i, 1).EntireRow.Hidden = True Then
          If rRng Is Nothing Then Set rRng = .Cells(i, 1) Else Set rRng = Union(rRng, .Cells(i, 1))
        End If
      Next i
      If Ws.FilterMode = True Then Ws.ShowAllData
      If Not cRng Is Nothing Then cRng.EntireColumn.Delete: Set cRng = Nothing
      If Not rRng Is Nothing Then rRng.EntireRow.Delete: Set rRng = Nothing
    End With
  Next Ws
End Sub

AnhThu-1976 · 17/5/18

HieuCD đã viết:

Chạy code

Mã:

Sub GPE()
  Dim Ws As Worksheet, i As Long
  Dim cRng As Range, rRng As Range
  For Each Ws In ThisWorkbook.Worksheets
    With Ws.UsedRange
      For i = 1 To .Columns.Count
        If .Cells(1, i).EntireColumn.Hidden = True Then
          If cRng Is Nothing Then Set cRng = .Cells(1, i) Else Set cRng = Union(cRng, .Cells(1, i))
        End If
      Next i
      For i = 1 To .Rows.Count
        If .Cells(i, 1).EntireRow.Hidden = True Then
          If rRng Is Nothing Then Set rRng = .Cells(i, 1) Else Set rRng = Union(rRng, .Cells(i, 1))
        End If
      Next i
      If Ws.FilterMode = True Then Ws.ShowAllData
      If Not cRng Is Nothing Then cRng.EntireColumn.Delete: Set cRng = Nothing
      If Not rRng Is Nothing Then rRng.EntireRow.Delete: Set rRng = Nothing
    End With
  Next Ws
End Sub

Bạn cho hỏi thêm có cách nào xóa luôn các dòng hay cột đang Group (đang Group, nghĩa là đang ở chế độ hide )không?

Code xóa toàn bộ các dòng và cột ẩn (ẩn = Filter) của tất cả các sheet trong 1 file (1 người xem)

Người dùng đang xem chủ đề này

Thành viên tích cực

Thành viên tích cực

Huyền thoại GPE

Thành viên tích cực

Huyền thoại GPE

Huyền thoại GPE

Cạo Rồi Khỏi Gội

Thành viên tích cực

Thành viên tích cực

Cạo Rồi Khỏi Gội

Thành viên tích cực

File đính kèm

Ăn cùng góc phố

Cạo Rồi Khỏi Gội

Huyền thoại GPE

Cạo Rồi Khỏi Gội

Huyền thoại GPE

Thành viên tích cực

Huyền thoại GPE

Chuyên gia GPE

Thành viên tích cực

Đếm ngược thời gian