Amending code to copy formulas in hidden cells when creating a new line

Question

I am using Excel to track results returned for a lab programme. There are multiple users who use the spreadsheet and we have created custom views for each of them. When they down load a new record they add a line using the VBA script below, which works great but there are some cells that need to be hidden from them that have formula in and need to copy down but don't.

Is there something I can add to this script to ensure formulas in hidden cells are also copied?

Sub New_Delta()

  ' Go to last cell
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

  ' Copy formula from cell above
  Rows(Selection.Row - 1).Copy
  Rows(Selection.Row).Insert Shift:=xlDown

End Sub

Answer 1

A: copy last row ... simple version

If the last used row is completely visible, and the first column always contains anything, I suggest this:

Private Sub CopyLastRow()
    Dim r As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet ' or whatever sheet

    ' following two line are referred as "middle part" later
    Set r = ws.Cells(ws.Rows.Count, 1).End(xlUp)
    r.EntireRow.Copy r.EntireRow.Offset(1, 0) ' copy content and format

    Set r = Nothing
    Set ws = Nothing
End Sub

If the last used row contains some empty cells somewhere, it might be better to determine the last used row by the (slower) Range.Find (exchange the middle part by following):

If WorksheetFunction.CountA(ws.Cells) > 0 Then
    Set r = ws.Cells.Find(What:="*", _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious)
    r.EntireRow.Copy r.EntireRow.Offset(1, 0)
End If

B: ... with hidden columns

Above also works, if you just have some "normal" hidden columns or grouped columns which are hidden.

C: ... but last row is hidden by a filter

In such cases, both code variants above would find the last visible row and copy it to its next (=hidden) row by overwriting its content - of course unwanted!

If you change the middle part by following code, it works, if there is absolutely nothing below the last "used" row.
(for concerns about UsedRange see Error in finding last used cell in VBA)

Set r = ws.Cells(ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1, 1)
r.EntireRow.Copy r.EntireRow.Offset(1, 0)

D: ... but there are filtered rows and hidden columns

If you have filtered rows and additionally have hidden columns, either an error message about "multiple selection" raises or just the visible columns are copied and mess up. I suggest to un-filter before.

E ... but there are merged cells

Horizontally merged cells will be copied as is.

But if you have vertically (or vertically and horizontally) merged cells, they will be copied unmerged and contain nothing, as the content of merged cells is stored in the upper left cell (and therefore not copied, if the last row is copied):

In this case you can copy the row and merge the cells:

Set r = ws.Cells(ws.Rows.Count, 1).End(xlUp)
r.EntireRow.Copy r.EntireRow.Offset(1, 0) ' copy content and format

Dim c As Range
Dim CurrentColumn As Long
Dim MergedColumnCount As Long
For CurrentColumn = 1 To ws.UsedRange.Columns.Count
    Set c = ws.Cells(r.Row, CurrentColumn)
    If c.MergeArea.Rows.Count > 1 Then
        MergedColumnCount = c.MergeArea.Columns.Count
        c.MergeArea.Resize(c.MergeArea.Rows.Count + 1, c.MergeArea.Columns.Count).Merge
        CurrentColumn = CurrentColumn + MergedColumnCount - 1
    End If
Next CurrentColumn

As the border (maybe other formats also) of the merged cells may vary from the row above, you may restore it additionally.