在选定范围内选择列VBA [英] Select column in a selected range VBA

问题描述

我正在尝试过滤表并一次只复制过滤范围内的一列.

I'm trying to filter my table and copy just a column in my filtered range at a time.

我试图使第2列在我的范围内，但它仅返回第一个单元格.我还尝试了.entireColumn，但它返回整列，而不是所选的整列.

I tried to get column 2 in my range but it returns only the first cell. I also tried .entireColumn, but it returns the entireColumn, not the selected entire column.

Sub FillStocks(ByVal myDate As Date, ByVal name As String)

  strDate = Format(myDate, "dd/MM/yyyy")

  'Filter
  With Sheets("BD_Sheet")
      Set tableRange = .UsedRange

      With tableRange
          Call .AutoFilter(1, strDate)
          Call .AutoFilter(2, name)
      End With

      On Error Resume Next
      Set selectedRange = tableRange.SpecialCells(xlCellTypeVisible)
      On Error GoTo 0

      With tableRange
          Call .AutoFilter(2)
          Call .AutoFilter(1)
      End With
  End With

  '-----Do something with my range  
  selectedRange.Columns(2).Copy Sheets("MyReport").[A1] 'fail to copy selected values in column B
  selectedRange.Columns(5).Copy Sheets("MyReport").[A2] 'fail to copy selected values in column E      
End Sub

示例:

我的BD在"A1:O1000"范围内.

I have a BD in the range "A1:O1000".

按日期和名称过滤后，我从selectedRange.Address获取了Range("$ A $ 1:$ O $ 1，$ A $ 78:$ O $ 172")

After I filtered by date and name, I got Range("$A$1:$O$1,$A$78:$O$172") from selectedRange.Address

我要复制B列到我选择的范围内，所以Range("B1"，"B78:BB172").

I Want to copy column B form my selected range, so Range("B1","B78:BB172").

推荐答案

您的selectedRange是一个多区域选择-在这种情况下，columns属性仅返回范围的第一个区域中的列.

Your selectedRange is a multiple area selection - in this instance, the columns property returns columns from only the first area of the range.

相反，使用您感兴趣的所选范围和整个列的交集-例如使用

Instead, use the intersection of your selectedRange and the entirecolumn your are interested in - for instance with

Application.Intersect(selectedRange.columns(2).Entirecolumn,selectedRange)

这篇关于在选定范围内选择列VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！