Copy from autofiltered range with hidden columns doesn't include hidden cells' content

1
votes

My goal is to create a macro that inserts a blank row just above a user-selected cell or row and copies formulas from the selected row into the new inserted row. Only formulas are to be copied, not constants. Cells with constant values in the copied row are left blank in the inserted row.

Code is pretty basic. Here it is:

Sub InsertLine()

Selection.EntireRow.Insert
Selection.Offset(1, 0).EntireRow.copy Selection.EntireRow
Selection.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

End Sub

This works great in all but one situation.
If the range contains both an activated auto-filter and hidden columns, then the content of the hidden cell on the copied row does not get selected by the copy method and is hence not transferred to the inserted line. If the range is only auto-filtered or only has hidden columns, then all works good.

This behavior is well documented here but no solution is offered. I haven't found anything else on the web.

This behavior is identical when performing cut-and-paste (ctrl-C/ctrl-V ) operations manually directly from the spread sheet.
So i would say it's not a VBA issue. But the solution may lie with VBA.
Can anyone offer any solutions or workarounds?

Thanks,

F

1
vbaexcel
the solution for such problem is to use loop which will go For each cell in ~range-to-copy~. In such situation no matter if there are any hidden or filtered cells. – Kazimierz Jawor

1 Answers

2
votes
Sub InsertLine()

    Dim src As Range, srcUsed As Range, c As Range

    Set src = Selection.Cells(1).EntireRow
    src.Insert

    Set srcUsed = Application.Intersect(src, src.Parent.UsedRange)
    For Each c In srcUsed.Cells
        c.Copy c.Offset(-1, 0)
        If Not c.HasFormula Then c.Offset(-1, 0).ClearContents
    Next c

End Sub