VBA 判断及隐藏空行空列

On_fire · 发表于 2023-3-21 15:10

VBA 判断及隐藏空行空列

*

Sheet1是源数据

*

代码逻辑如在

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

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

*

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

liulang0808 · 发表于 2023-3-21 15:19

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 · 发表于 2023-3-21 15:20

供参考。。。。。。

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

Sub test0()
Dim i As Long, j As Long, r As Range, c As Range
Sheet2.Activate
i = Cells.Find("*", , , , 1, 2).Row
j = Cells.Find("*", , , , 2, 2).Column
With Range("A1", Cells(i, j))
For Each r In .Rows
r.EntireRow.Hidden = WorksheetFunction.CountA(r) = 0
Next
For Each c In .Columns
c.EntireColumn.Hidden = WorksheetFunction.CountA(c) = 0
Next
End With
End Sub

复制代码

lizhipei78 · 发表于 2023-3-21 15:50

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

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

tanglf188 · 发表于 2023-3-21 15:55

sxbjzjf110 · 发表于 2023-3-21 16:18

sxbjzjf110 · 发表于 2023-3-21 16:21

gwjkkkkk · 发表于 2023-3-21 19:58

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 · 发表于 2023-3-21 20:00

参与一下。。。

		自动登录	找回密码
密码			免费注册

[求助] VBA 判断及隐藏空行空列

评分

评分

评分

评分

评分