Dynamic Named Formula for a table, not just one column

[malik641]

I've heard a couple times about people needing a code to copy/paste data that never had equal amounts of rows. So why not create a defined name that traps the table for you? It would make coding more efficient, IMO.

The usual formula for a dynamic named formula would be:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

This works for one column. But what about a table where the amount of rows is different for each column?

I'm sure there's an easier way to do this, but here is my formula to enclose an entire table with uneven rows of data (with a KNOWN amount of columns, this case being A:C):

=OFFSET(Sheet1!$A$1,0,0,MAX(COUNTA($A:$A),COUNTA($B:$B),COUNTA($C:$C),COUNT A(Sheet1!1:1))

But when I try to define this name (by pressing "ADD"), it messes up the text with dividor signs and what looks like CHAR(10) when you don't have the cell formatted for wrapped text.


Now coming back to the "making code more efficient", here's why I think that:
-The code would be reduced TREMENDOUSLY. If there can be a defined named formula that does what I'm trying to do, here's what the code would look like:

[VBA] Option Explicit
Sub CopyPaste()
ThisWorkbook.Names.Add Name:="DynamicTable", _
RefersTo:="=OFFSET(Sheet1!$A$1,0,0,MAX(COUNTA($A:$A),COUNTA($B:$B),COUNTA($ C:$C),COUNTA(Sheet1!1:1))"
Sheets("SheetToCopy").Range("DynamicTable").Copy
Sheets("SheetToPaste").Range("A1").PasteSpecial xlAll
End Sub
[/VBA]
Now I can't test this code out....but it SHOULD work, right?

Just a thought

[geekgirlau]

Joseph, I haven't tested this but I know that using several of the "Offset" ranges does slow things down dramatically in a large workbook, as Excel needs to effectively "calculate" the range each time it's used. While your sample on the surface of it looks to be logical, I would wonder about the increase in overhead this would add.

In my experience most data ranges (even where not every field is filled) do have a key field which is always present, and it's not always column A. I use that column for the Height parameter of the formula.

By the way, I think your code is missing a bracket:

[VBA]RefersTo:="=OFFSET(Sheet1!$A$1,0,0,MAX(COUNTA($A:$A),COUNTA($B:$B),COUNTA($ C:$C)),COUNTA(Sheet1!1:1))"[/VBA]

[mdmackillop]

Hi Joseph,
Based on your code
[VBA]
Sub CopyPaste()
ThisWorkbook.Names.Add Name:="DynamicTable", _
RefersTo:="=SheetToCopy!$A$1:" & [A1].Offset(Application.WorksheetFunction.Max( _
Application.WorksheetFunction.CountA(Columns("A:A")), _
Application.WorksheetFunction.CountA(Columns("B:B")), _
Application.WorksheetFunction.CountA(Columns("C:C")) - 1), 2).Address
Range("DynamicTable").Copy
Sheets("SheetToPaste").Range("A1").PasteSpecial xlAll
End Sub
[/VBA]

[Zack Barresse]

I have never liked the COUNTA method for getting such a dynamic range. I prefer something like xld has here: http://www.xldynamic.com/source/xld.LastValue.html

[malik641]

geekgirlau, thanks for the correction Missed that

MD, nice submission .
Too bad it has to be renamed evertime values change though...

And Zack, good to know I'll read that in further detail later.


....A formula would be nice, though. One that would change the table size if you were to add more rows & columns.

[Justinlabenne]

Just for fun: Creates single column named ranges using Row 1 text as the named range, then builds a dynamic table range using all the single ranges. Not perfected or thouroughly tested, (or even useful because you can't copy the entire table at once)

[VBA]Public Sub BuildDynamicRanges()
Const szTableName As String = "rgnTable"
Dim lRow As Long
Dim lCol As Long
Dim rng As Range
Dim ws As String
Dim szNewRange As String
ws = ActiveSheet.Name

lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim x As Long
For x = 1 To lCol
If Cells(1, x).Value <> "" Then
lRow = Cells(Rows.Count, x).End(xlUp).Row

With Sheets(ws)
ActiveWorkbook.Names.Add .Cells(1, x).Text, .Range(Cells(1, x), Cells(lRow, x))
szNewRange = szNewRange & "," & Replace$(Names(.Cells(1, x).Text), "=" & ActiveSheet.Name & "!", "")
End With

End If
Next x
szNewRange = Right$(szNewRange, Len(szNewRange) - 1)
ActiveWorkbook.Names.Add szTableName, "=" & ActiveSheet.Name & "!" & szNewRange
Set rng = Nothing
End Sub[/VBA]

[malik641]

I stand corrected to one of my posts:

=OFFSET(Sheet1!$A$1,0,0,MAX(COUNTA($A:$A),COUNTA($B:$B),COUNTA($C:$C),COUNT A(Sheet1!1:1)))

But when I try to define this name (by pressing "ADD"), it messes up the text with dividor signs and what looks like CHAR(10) when you don't have the cell formatted for wrapped text.

This DOES work when you type it in manually...the only thing I don't like about this is if you have LOTS of columns...then you might run out of space in the formula because of too many characters.

I will still work on this, but untill then.....
I submit this formula as a dynamic table based on the known number of columns used...I'll work on the unknown columns part.

[mdmackillop]

Hi Joseph,
I submitted my code that way to follow your example. The following uses the activecell and an inputbox for you to enter the last column, which is a bit more flexible. This could easily be changed to a range selection or other method as required.
Regards
Malcolm
[VBA]
Sub CopyPaste()
Dim StCol As Long, EndCol As Long, i As Long, j As Long
StCol = ActiveCell.Column()
EndCol = Cells(1, InputBox("Enter last column")).Column
j = 1
With Application.WorksheetFunction
For i = StCol To EndCol
j = .Max(j, Cells(65536, i).End(xlUp).Row)
Next
End With
ThisWorkbook.Names.Add Name:="DynamicTable", _
RefersTo:="=Sheet1!" & ActiveCell.Address & ":" & _
ActiveCell.Offset((j - ActiveCell.Row()), EndCol - StCol).Address
Range("DynamicTable").Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlAll
Application.CutCopyMode = False
End Sub

[/VBA]

[tkaplan]

for dynamically naming a range for unlimited number of columns and rows, i use:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

this eliminates having to use max, and does not put a limit on number of columns.

[Zack Barresse]

The COUNTA method works, yes, but will not compensate for non-contiguous data.

[mdmackillop]

Modified.

[Zack Barresse]

And how could I not mentione the beautiful article by our own Johnske re Last Row ..

http://www.vbaexpress.com/forum/arti...ticle&artid=53

[malik641]

for dynamically naming a range for unlimited number of columns and rows, i use:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

this eliminates having to use max, and does not put a limit on number of columns.

Yeah but that only works if you KNOW that column A will always have the most data out of any other column.

And I agree with Zack about the non-contiguous data that COUNTA doesn't take into consideration.

And thanks for the article Zack Kudos to Johnske