Solved

Excel : Resize named range based on data.

Posted on 2014-09-10
3
181 Views
Last Modified: 2014-09-11
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
0
Comment
Question by:conceptdata
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40314839
This will resize to the same size as the table on the other sheet:
Sub Makro1()
    Dim lngLastRow As Long
    Dim oList As ListObject
    
    With Sheets("C5_InvenItemGroup").ListObjects(1).DataBodyRange
    lngLastRow = .Row + .Rows.Count - 1
    End With
    ActiveSheet.ListObjects("Tabel99").Resize Range("$A$3:$D$" & lngLastRow)
End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:Rolf Hasselbusch
ID: 40314861
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.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40315939
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)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question