CODEWITHSUNDEEP
excelvba
Makah
Makah

Reputation: 4513

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.

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

Example:

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

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

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

Upvotes: 0

Views: 2763

Answers (1)

IAmDranged
IAmDranged

Reputation: 3020

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)

Upvotes: 1

Related Questions