0

I have below code where it filter 1 in AT column from worksheet1 and try to copy data in AS,E,C columns and paste in worksheet2. Problem is, it is not able to copy entire data in E and C if there are blanks in the middle as end(xldown) is only able to copy until first blank cell. Can someone help me to correct it so that once 1 is filtered in column AT then code should be able to copy data in columns E and C from start to end.


        Worksheets(worksheet1).select
Rows("3:3").select
Selection.autofilter
Range(AT).slect
Activehseet.Range("$A$3:$AT$1185").Autofilter Field:=46, Criteria:="1"
Range("AS4").Select
Range(Selction, Selection.End(xldown)).select
Selection.copy
  • same as above to copy data in E and C columns
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Sharmila V
  • 1
  • 6

1 Answers1

0

First of all it is absolutely necessary to avoid using Select in Excel VBA in order to produce reliable code.

Second issue is that as you found out Selection.End(xlDown) will go down until the next free cell. In order to find the very last used cell in a column you need to start from the very last cell and go xlUp:

Set LastUsedCell = Cells(Rows.Count, "AS").End(xlUp)

So to get rid of all .Select and Selection statements first declare a variable that works as a reference to your worksheet:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("SheetName")

Now you can access this worksheet using ws without needing to select it. Then you need to find the last used row in column AT. Otherwise your filter using Range("$A$3:$AT$1185") is limited to 1185.

Dim LastUsedCell As Range
Set LastUsedCell = ws.Cells(ws.Rows.Count, "AS").End(xlUp)

ws.Range("$A$3", LastUsedCell).Autofilter Field:=46, Criteria:="1"

If you now want to copy only the filtered data you need to make sure that you get only the visible cells in your filtered range, because ws.Range("$A$3", LastUsedCell) will refer to all cells in that range not only the ones you filtered.

Dim FilteredData As Range
On Error Resume Next  'next line will throw an error if there are no visible cells
Set FilteredData = ws.Range("$A$4", LastUsedCell).SpecialCells(xlCellTypeVisible)
On Error Goto 0  'don't forget to re-enable error reporting!

Finally you need to test if visible cells were found:

If FilteredData Is Nothing Then
    MsgBox "No filtered data found"
    Exit Sub
End If

FilteredData.Copy
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thank you for the response Peh but can you also help me to pick data from AS and E ,C once we apply filter in AT. I think above filtereddata.copy copies all the data in the worksheet after the filter right!! – Sharmila V Mar 11 '20 at 08:30
  • @SharmilaV Analog to the first column: `Set FilteredData = ws.Range("E4", "E" & LastUsedCell.Row).SpecialCells(xlCellTypeVisible)` – Pᴇʜ Mar 11 '20 at 08:43