VBA 判断及隐藏空行空列

On_fire

VBA 判断及隐藏空行空列

*

Sheet1是源数据

*

代码逻辑如在

1.      判断工作表的空行及空列 ( 模拟:Sheet1(2)标黄行列 )

2.      隐藏所有空行及空列 ( 模拟:Sheet1(3) )

*

恳请大神, 老师帮忙…谢谢!

liulang0808

Sub test()
    Application.ScreenUpdating = False

    Set rngx = ActiveSheet.UsedRange
    For j = rngx.Cells(1).Row To rngx.Cells(rngx.Cells.Count).Row
        If WorksheetFunction.CountA(Rows(j)) = 0 Then
            Rows(j).Hidden = True
        End If
    Next j
    For j = rngx.Cells(1).Column To rngx.Cells(1).Column + rngx.Columns.Count - 1
        If WorksheetFunction.CountA(Columns(j)) = 0 Then
            Columns(j).Hidden = True
        End If
    Next j
   
    Application.ScreenUpdating = True
End Sub

liulang0808

供参考。。。。。。

fzxba

1. Sub test0()
   
2.   Dim i As Long, j As Long, r As Range, c As Range
   
3.   Sheet2.Activate
   
4.   i = Cells.Find("*", , , , 1, 2).Row
   
5.   j = Cells.Find("*", , , , 2, 2).Column
   
6.   With Range("A1", Cells(i, j))
   
7.     For Each r In .Rows
   
8.       r.EntireRow.Hidden = WorksheetFunction.CountA(r) = 0
   
9.     Next
   
10.     For Each c In .Columns
    
11.       c.EntireColumn.Hidden = WorksheetFunction.CountA(c) = 0
    
12.     Next
    
13.   End With
    
14. End Sub

复制代码

lizhipei78

fzxba 发表于 2023-3-21 15:35

代码完全看不懂，能解释一下吗？

tanglf188

sxbjzjf110

sxbjzjf110

gwjkkkkk

Option Explicit
Sub test()
    Dim ar, i&
    With ActiveSheet.UsedRange
        ar = .Value
        For i = 1 To UBound(ar, 2)
            If Join(Application.Transpose(Application.Index(ar, , i)), "") = "" Then
                .Item(i).EntireColumn.Hidden = True
            End If
        Next i
        For i = 1 To UBound(ar)
            If Join(Application.Index(ar, i), "") = "" Then
               .Item(i, 1).EntireRow.Hidden = True
            End If
        Next i
    End With
End Sub

gwjkkkkk

参与一下。。。