Thu 9 Jun 2011, 16:02Delegate Nicole said...
Nicole has attended:
Excel VBA Intro Intermediate course
Dynamic named ranges
I would like to know the best practice to set up dynamic named ranges when importing data from an acess database into excel.
Fri 10 Jun 2011, 12:38Trainer Stephen said...
RE: Dynamic named ranges
Hi Nicole
The following procedure dynamically assesses the extent of an excel table, assigns that range to a range object and then names that range.
Every time the code is run, the range updates dynamically
Sub DynamicRanges()
Dim rngDynRng As Range
Set rngDynRng = Sheets("Data Validations").Range(Cells(3, 1), _
Cells(Range("A3").CurrentRegion.Rows.Count, Range("A3").CurrentRegion.Columns.Count))
rngDynRng.Name = "MyTable"
End SubHope this is what you were looking for
Regards
Stephen
