Dynamic name

[pedjvak]

Hi there
I have an Excel file which update every day
for example : range A1:F12 filling by data today
tomorrow my range will be extended to A1:F28 and the day after A1:F39 and again and again
now could you please show me how I can define a range name for that ?

[HaHoBe]

Hi, pedjvak,

easiest way would be to use a Table which would auto update to the number of cells filled.

Dim lngLast As Long
lngLast = Range("A" & Rows.Count).End(xlUp).Row
MsgBox "Range for today covers 'A1:F" & lngLast & "'", vbInformation

Ciao,
Holger

[pedjvak]

Dear HaHoBe
could you plz write your solution completely because I am new in excel
thanks

[HaHoBe]

Hi, pedjvak,

if you had indicated what you need that information for IŽd done it in my first post.

Sub EF965621_1()
'Display range in MsgBox
'always works on the active sheet
Dim lngLast As Long
lngLast = Range("A" & Rows.Count).End(xlUp).Row
MsgBox "Range for today covers 'A1:F" & lngLast & "'", vbInformation
End Sub

Sub EF965621_2()
'Copy the range to the clipboard
'always works on the active sheet
Range("A1:F" & Range("A" & Rows.Count).End(xlUp).Row).Copy
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Ciao,
Holger

[ratcat]

G'day

Non vba solution http://support.microsoft.com/kb/830287

[martindwilson]

A1:INDEX(F:F,COUNTA(A:A)) wound generate a dynamic named range based on the length of data in column a no spaces allowed
or maybe
A1:INDEX(F:F,LOOKUP(2,1/(A1:A60000<>""),ROW(A1:A60000))
use like
=INDEX(A1:INDEX(F:F,LOOKUP(2,1/(A1:A60000<>""),ROW(A1:A60000))),MATCH(H1,A1:A60000,0),6)
or
=COUNTIF(A1:INDEX(F:F,LOOKUP(2,1/(A1:A60000<>""),ROW(A1:A60000))),"z")