How to copy filtered data without copying empty cells

Question

Scenario

I have an excel sheet with a lot of values. I am using some macro to filter those values.

What I need

I need to copy only the filtered values from current sheet to another sheet. I am doing the following way

Sub filterCopy()
    Selection.SpecialCells(xlCellTypeVisible).Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Issue

The above code doing exactly what is supposed to do by copying only the visible cells. But the problem is, it is copying entire sheet including all the blank cells upto row number 1048480 and even more. But my data is only upto row number 12. How should I copy only the visible cells that contains data and not all those blank cells?

Pictures

Filtered Sheet After copied filtered data to another sheet

Would `Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Sheets(2).Range("A1")` work for you? It would at least get rid of `.Select` which you should always [avoid](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — JvdV, Aug 31 '18 at 08:38
When use `A2:A`, it is only copy column A whereaby I need to copy all the filtered non empty cell. I use `Activesheet.UsedRange.Copy` and it seems working. But I am not sure if this will cause any other problems — Anu, Aug 31 '18 at 08:56
Yes I overlooked that, see provided answer to tackle that problem. — JvdV, Aug 31 '18 at 09:12
@JvdV Thank you. Able to solve the issue as I stated in below comment — Anu, Aug 31 '18 at 09:20

Elcan · Accepted Answer · 2018-08-31T09:00:30.750

1

Your code doesn't take into account the last cell in the vertical space, and actually copies everything in the A1 range.

The solution is first, to avoid Select as it provides low performance, then to use

Cells(Rows.Count, "A").End(xlUp).Row

to get the last row in the A column. This gives us

Sub filterCopy()
    Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Sheets(2).Range("A1")
End Sub

To copy the full table with the filtered rows, then use

Sub filterCopy()
    Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Copy Sheets(2).Range("A1")
End Sub

edited Aug 31 '18 at 09:00

answered Aug 31 '18 at 08:52

Elcan

814
13
27

In fact I need not only A column, but also all filtered columns that contains value. Is there any issue in using `Activesheet.UsedRange.Copy` – Anu Aug 31 '18 at 08:57
1

@Anu See updated answer. `.SpecialCells(xlCellTypeVisible).Copy` works on any range, so `Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Copy` will work to copy the full table (but only the visible rows) – Elcan Aug 31 '18 at 09:01
Thank you. By combining your and above person comment, I am able to do the job in one line as below `Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy Sheets(2).Range("A1")` – Anu Aug 31 '18 at 09:18

How to copy filtered data without copying empty cells

1 Answers1