Link to home
Start Free TrialLog in
Avatar of conceptdata
conceptdataFlag for Denmark

asked on

Excel : Resize named range based on data.

Hi
I have a sheet with a named range. (with 1 data line and 1 headline)
I have to a sheet with data in it.

If I resizes the range with the mouse, I get this code udsing record macro :
Sub Makro1()
    ActiveSheet.ListObjects("Tabel99").Resize Range("$A$3:$D$14")
End Sub

Open in new window

BUT, I need a VBA code that resizes the Range based on data lines fra the data sheet.
In this case not row 14 but 16......

Attached : Excel
EE-Example.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rolf Hasselbusch
Rolf Hasselbusch
Here you'll find Examples for Dynamic Ranges as Formulas and in VBA (at the bottom).

If you need further assistance i hope some more VBA addicted Experts take a look at this question.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
Here are two formulas for dynamic ranges. In this case the data is in column A.

Range has no heading
=OFFSET('Sheet Name'!$A$1,0,0,COUNTA('Sheet Name'!$A:$A),1)

Range has a heading
=OFFSET('Sheet Name'!$A$2,0,0,COUNTA('Sheet Name'!$A:$A)-1,1)