excel - Excel VBA单列仅可见单元格到多列（网格）

Question

你们能否帮助我并建议代码编辑以使代码在过滤列时也适用于可见单元格。目标数据应转换为连续的多列到列视图。下面的代码按预期工作，除了只能使用过滤的内容，如果列被过滤，则目标视图有空白单元格。很乐意实现没有空白的视图。

Private Sub CommandButton1_Click()
Dim xLRow As Long
Dim xNRow As Long
Dim i As Long
Dim xUpdate As Boolean
Dim xRg As Range
Dim xOutRg As Range
On Error Resume Next
Set xRg = ThisWorkbook.Sheets("Sheet3").Range("A2:A25")
If xRg Is Nothing Then Exit Sub
Set xOutRg = ThisWorkbook.Sheets("Sheet3").Range("A30")
xUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
xLRow = xRg.Rows.Count
For i = 1 To xLRow Step 5
    xRg.Cells(i).Resize(5).Copy
    xOutRg.Offset(0, xNRow).PasteSpecial Paste:=xlPasteValues, Transpose:=False
    xNRow = xNRow + 1
Next
Application.ScreenUpdating = xUpdate End Sub

提前致谢。

标签： excelvbacellvisible