Excel 2016
I have a data input tab that consists of 4 rows column B, D, F, and H. In each of these rows contains a dynamic range that is defined by =OFFSET(Input!$H$1,0,0,COUNTA(Input!$H$2:$H$1048576),1) as an example. I have written VBA code that pulls entered data from certain cells J6, N6, J13 and N13 and places the cell value into the bottom of these ranges. The problem is that the first cell in each of these ranges is a date and eventually becomes outdated and must be removed.
Parts of my VBA code reference these ranges as whole ex: Worksheets("input").Range("Period").Copy
So when these old cells at the top are removed then I get =OFFSET(Input!REF!,0,0,COUNTA(Input!$H$2:$H$1048576),1) because the cells have been deleted and are no longer referencing that cell. The reason I have to copy to the first cell in the column is because it is an ordered date list and needs to be copied in that order.
I am new to using dynamic ranges can someone please help?
Thanks