How to define a new database area using varible row/column nr 2

[hannal]

Hi!

I’m trying to do a macro that define a new database area from cell A1 to the cell at the r:th row and c:th column. How can I use a reference that takes variables as argument? Like for example, Cells(r, c)?

I can select the new area with this code.

range(Cells(10, 1), Cells(r, c)).Select

But how do I set the new database area to the selected area? If a record a macro I get:

    ActiveWorkbook.Names.Add Name:="Portfolios", RefersToR1C1:= "=Port!R10C1:R123C36"

But in this code the row and column numbers are fixed.

I’ve tried to use the

convertFormula

formula to convert between A1 and R1C1 (thinking I could use cells(r,c)), but it didn’t work, perhaps I don’t understand the syntax.

Can someone help me?

Regards /HL

 

[xlbo]

ActiveWorkbook.Names.Add Name:="Portfolios", RefersToR1C1:= "=Port!R10C1:R" & r & "C" & c

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[hannal]

Thanks!
Works perfect. I'm new at VBA and being able to use variables in references opens up a lot of possibilities. None of the books that I use mention this syntax. A star from me!
/HL

 

[SkipVought]

Hi,

I do not lie the R1C1 format, and prefer using Cells(r, c), where r is row and c is column.

So here is a version using Cells...

ActiveWorkbook.Names.Add Name:="Portfolios", RefersTo:= "=Port!" & Range(Cells(10, 1), Cells(r, c)).Address

If you already have a table with data, then you could generalize this code even further, as long as...
1. The table is isolated from any other data (I always put one table on a sheet starting in A1)
2. All the data in the table is contiguous (no empty rows or columns)
3. Each column of data has a unique heading.
4. the Sheet name becomes the Data Range Name...

ActiveWorkbook.Names.Add Name:= ActiveSheet.Name, RefersTo:= "='" & AcheveSheet.Name & "'!" & Cells(1,1).CurrentRegion.Address

If I have several tables like this in my workbook, I use a sheet naming convention prefix of "tbl" -- tblPortfolios. Then, in the Workbook_SheetChange event, I call the TableRename macro like this...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Left(Sh.Name, 3) = "tbl" Then _
        RenameTable
End Sub

so anytime the data in a tbl sheet is changed, the range name will automatically be adjusted to include all the data.

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[hannal]

SkipVought:

Hi! Thanks, I wanted to use the cells method but couldn't work out the syntax. Unfortunately I have emtpy rows inside this dataarea, so I'll have to settle with the first tip although the second one would have been great to use!

/HL