Copy hidden row and paste unhidden after last row

[Hudco]

Hi people,
I am trying to copy a row and paste it after the last row of data but retain only formula and no data. Have since found Excel won't do this so I have created a hidden row with the formula and no data.

This is row 5.

I then want to copy this row and paste or insert it below the last row with data. The cell A5 does not have a formula or data. The following is what I have but the new row is hidden. Any thoughts/help much appreciated.

[CODE=vba]
    Rows("5:5").Select
    Selection.Copy
    Range("A5").Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.Insert Shift:=xlDown

Thank you in anticipation

 

[Peter_SSs]

The cell A5 does not have a formula or data.

I have assumed then that B5 does and that column B can be used to determine the last row of data.
Try not copying the whole row, something like this.

Sub Copy_Row()
  Range("A5:Z5").Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
End Sub

 

[jolivanes]

Do I understand you right that you want to copy the formula in Range A5 to the first empty cell below the used data in Column A?
You also mention to copy the Row but in your code in post #1 it only shows that single cell (A5)
How many cells to the right need to be copied/pasted?

 

[Peter_SSs]

you want to copy the formula in Range A5

I thought this clearly stated that there is no formula (or anything else) in A5?

The cell A5 does not have a formula or data.

You also mention to copy the Row but in your code in post #1 it only shows that single cell (A5)

This part of the OP's code copies the whole row for me

Rows("5:5").Select
Selection.Copy

 

[jolivanes]

I guess I should read past the "Range("A5").Select" statement.

 

[Hudco]

I have assumed then that B5 does and that column B can be used to determine the last row of data.
Try not copying the whole row, something like this.

Sub Copy_Row()
  Range("A5:Z5").Copy Destination:=Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
End Sub

Hi Peter SSs,

On re-reading my original post is not overly clear. Your code worked - Thanks.
ps My apologies for the delayed reply - busy weekend.

 

[Peter_SSs]

You’re welcome. Thanks for the follow-up.