仅复制VBA中的可见范围? [英] Only copy visible range in VBA?

问题描述

我遇到了一个问题，我无法仅将可见的单元格复制到新的工作表中.我能够获得lastrow，但是除了每列的第一个单元格之外，我在每个单元格上都获得#N/A.我只想复制可见的单元格.如果可能的话，我也想只将信息放在可见行上?

I'm running into an issue where I'm unable to copy only visible cells to a new sheet. I'm able to get the lastrow, but I get #N/A on every cell except the first for each column. I want to just copy the visible cells. I'd also like to only put information on visible rows too, if possible?

请在下面查看我的代码:

Please see my code below:

Sub Importe()
lastRow = Worksheets("Sheet1").Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row

Worksheets.Add

With ActiveSheet
  Range("A1:A" & lastRow).Value2 = _
  ActiveWorkbook.Worksheets("Sheet1").Range("H1:H" & lastRow).SpecialCells(xlCellTypeVisible).Value
  Range("B1:B" & lastRow).Value2 = _
  ActiveWorkbook.Worksheets("Sheet1").Range("E1:E" & lastRow).SpecialCells(xlCellTypeVisible).Value
End With

End Sub

推荐答案

类似于.Value2 = .Value的方法不适用于可见类型的特殊单元格，因为…

Something like .Value2 = .Value doesn't work on special cells of type visible, because …

…例如如果lastRow = 50并且有hiddenRows = 10，那么...

… e.g. if lastRow = 50 and there are hiddenRows = 10 then …

• 您的来源Range("H1:H" & lastRow).SpecialCells(xlCellTypeVisible)
  有lastRow - hiddenRows = 40行
• 但您的目的地是Range("A1:A" & lastRow).Value2
  有lastRow = 50行.

• your source Range("H1:H" & lastRow).SpecialCells(xlCellTypeVisible)
  has lastRow - hiddenRows = 40 rows
• but your destination Range("A1:A" & lastRow).Value2
  has lastRow = 50 rows.

首先，您减去可见行，因此它们的大小不同.因此.Value2 = .Value不起作用，因为您不能仅用40个源行填充50行.

On the first you subtract the visible rows, so they are different in size. Therefore .Value2 = .Value doesn't work, because you cannot fill 50 rows with only 40 source rows.

但是您可以做的是Copy和SpecialPaste

Option Explicit

Sub Importe()
    Dim lastRow As Long

    lastRow = Worksheets("Sheet1").Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row

    Worksheets.Add

    With ActiveSheet
       ActiveWorkbook.Worksheets("Sheet1").Range("H1:H" & lastRow).SpecialCells(xlCellTypeVisible).Copy
       .Range("A1").PasteSpecial xlPasteValues

       ActiveWorkbook.Worksheets("Sheet1").Range("E1:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy
       .Range("B1").PasteSpecial xlPasteValues
    End With
End Sub

尽管如此，我还是建议避免使用ActiveSheet或ActiveWorkbook，并参考例如ThisWorkbook的工作簿.我的建议:

Nevertheless I recommend to avoid ActiveSheet or ActiveWorkbook if this is possible and reference a workbook eg by ThisWorkbook. My suggestion:

Option Explicit

Sub Importe()
    Dim SourceWs As Worksheet
    Set SourceWs = ThisWorkbook.Worksheets("Sheet1")

    Dim DestinationWs As Worksheet
    Set DestinationWs = ThisWorkbook.Worksheets.Add

    Dim lastRow As Long
    lastRow = SourceWs.Cells(1, 1).SpecialCells(xlCellTypeVisible).End(xlDown).Row

    SourceWs.Range("H1:H" & lastRow).SpecialCells(xlCellTypeVisible).Copy
    DestinationWs.Range("A1").PasteSpecial xlPasteValues

    SourceWs.Range("E1:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy
    DestinationWs.Range("B1").PasteSpecial xlPasteValues
End Sub

这篇关于仅复制VBA中的可见范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！