Dynamic Range Help

[ljlundgren]

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

[mikerickson]

The easiest solution is don't delete those cells, blank them. If you are using Named Ranges, you need to get into the habit of not deleting certain "anchor" cells, lest you create #REF errors.

Another approach would be to use
=OFFSET(INDEX(Input!$H:$H,1,1),0,0,COUNTA(Input!$H$2:$H$1048576), 1)

[mdmackillop]

You can read the formula then reset the range name

Sub test()
Dim Fmla
Fmla = ActiveWorkbook.Names("Test").RefersTo
Rows(1).Delete
ActiveWorkbook.Names.Add ("Test"), RefersTo:=Fmla
End Sub

[ljlundgren]

Thank you, My issue is if I use blanks I am copying the range and the way I have my VBA code setup it will copy over the blanks and ruin my data pull.

I will try the second approach and see if that works.

[ljlundgren]

The easiest solution is don't delete those cells, blank them. If you are using Named Ranges, you need to get into the habit of not deleting certain "anchor" cells, lest you create #REF errors.

Another approach would be to use
=OFFSET(INDEX(Input!$H:$H,1,1),0,0,COUNTA(Input!$H$2:$H$1048576), 1)

This worked, thank you.

[mdmackillop]

I find it more convenient to use COUNTA(Input!$H:$H)-1 rather than the last row number.

[mikerickson]

I prefer to use the non-volatile form

=Input!$H$1:INDEX(Input!$H:$H, COUNTA(Input!$H:$H),1)

[mdmackillop]

Hi Mike
Never used Index in for Named Ranges. If looking at E2:G11 I came up with
=Input!$E$2:INDEX(Input!$1:$1048576,COUNTA(Input!$E:$E),COUNTA(Input!$1:$1) +COLUMN(Input!$E$2)-1)
Is there a more elegant solution? (I know I could replace the last term with 4)
Regards
Malcolm

[SamT]

I are coming to believe that should be a "Best Practice" to avoid the use of Worksheet.Names in code.

For example. Always change the Sheet's Code Name, then use only it, vice using the Sheets/Worksheets Collection.

In the case of Range names, I think it may be best to use the applicable sheet's Code as a Property_Get or a Public Function to return Ranges.
Example:
Sheets("index").CodeName = "IndexSht"
IndexSht Code(s)

Public Function GetRange_NoHeaderPlus1(HeaderName As String) As Object
Dim Col As Range
Set Col = Range(1:1).Find(HeaderName).EntireColumn
Set GetRange_NoHeaderPlus1 = Range(Col.Cells(2), Col(Cells(Rows.Count).End(xlUp).Offset(1))
End Function.

Usage in VBA

Set MyRange = IndexSht.GetRange_NoHeaderPlus1("Period")

Public Function RangePeriod() As Object
Dim Col As Range
Set Col = Range(1:1).Find("Period").EntireColumn
Set RangePeriod = Range(Col.Cells(2), Col(Cells(Rows.Count).End(xlUp))
End Function.

Usage in VBA

IndexSht.Range("J1").Copy IndexSht.RangePeriod.Cells(IndexSht.RangePeriod.Cells.Count + 1)
Set MyRange = IndexSht.RangePeriod

One can provide even more protection than Protect Sheet with
Sheet Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target.Rows(1), Rows(1)) is Nothing Then CheckHeaders
End Sub

Private Sub CheckHeaders()
MyHeaders = "Header1, Header2, Header3"
For Each Cel In Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft))
If Not InStr(MyHeaders, Cel) > 0 Then
Application.EnableEvents = False
UndoHeaders
Application.EnableEvents = True
Exit For
Next
End Sub

Private Sub UndoHeaders()
Application.Undo
End Sub

Note that that Combinations of Name and undo code "Should" allow the User to Move columns without breaking the Project

[mdmackillop]

Hi Sam
I can see the benefit of this practice in my own projects but I would be reluctant to build in CodeName changes to a solution. Using standard CodeNames is not the best for clarity/understanding if the code involves 3+ worksheets. Setting variable names wsSource=Sheet1 etc. gives readability without imposition on the Poster's setup.
Regards
Malcolm

[Jan Karel Pieterse]

I would consider converting the range into a table (Start tab, format as table). Then all range names can be defined as fixed range names by pointing them at the section of the table it needs to point to. Excel will handle the rest for you nicely (growing or shrinking # of rows, removing first row, ...).

[SamT]

Malcom,

No I would not recommend changing a VBA member's setup either.

I would only do it when beginning a Project, or as a paid Major Refactoring of one.

I did a small proect for a friend long ago. He had just enough background in Excel to be dangerous. He added Names for use in his Formulas, and when he didn't see any use for some of the Names my Code relied on, he deleted them! Then he complained that the Project no longer worked. That is the first time I used the No Sheet.Names method in my code.

Sam

PS: I don't trust any User, now. st