Macro to create named range

[tosti]

Hi,
I am a newbie and was hoping someone can assist me.

I have created a vba code that looks at a range of rows and checks until where row/columns is empty. It then creates a named range. Therefore, it picks up col A to Col E.

Is there a way to edit the code so it can pick up different columns and create a range. For example Col A to Col D and Col F.

Thanks
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 92px"><COL style="WIDTH: 64px"><COL style="WIDTH: 88px"><COL style="WIDTH: 70px"><COL style="WIDTH: 57px"><COL style="WIDTH: 57px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial; FONT-SIZE: 10pt">scenario_code</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Arial; FONT-SIZE: 10pt">company_code</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial; FONT-SIZE: 10pt">account_code</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial; FONT-SIZE: 10pt">dim1_code</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial; FONT-SIZE: 10pt">amount1</TD><TD style="BACKGROUND-COLOR: #00ccff; FONT-FAMILY: Arial; FONT-SIZE: 10pt">amount2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>2011A</TD><TD>A00</TD><TD style="TEXT-ALIGN: right">1000</TD><TD>abc</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>2011A</TD><TD>A00</TD><TD style="TEXT-ALIGN: right">2000</TD><TD>abc</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>2011A</TD><TD>A00</TD><TD style="TEXT-ALIGN: right">3000</TD><TD>abc</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">200</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sub CommandButton1_Click()
Dim Finale, Escl1, Escl2 As String
Dim rr, Xcol, Ycol, Ycol2 As Integer
Dim lUltimaCella As Range
Dim lNome As String
Dim lNumColonne As Integer
Dim lWbk As Workbook
 
Sheets("Sheet1").Select
'--------- COLUMN
For Each x In Worksheets("Sheet1").Range("A1:e1")
If x.Value = "" Then Exit For
Xcol = Xcol + 1
Next x
'---------- ROWS
For Each y In Worksheets("Sheet1").Range("A2:A65000")
If y.Value = "" Then Exit For
Ycol = Ycol + 1
Next y
'--------------------------------------
 
lNome = "DE-PIVOT"
lNumColonne = Xcol
 
Set lUltimaCella = ActiveSheet.Cells(2, 1)
While lUltimaCella.Value <> vbNullString
    Set lUltimaCella = lUltimaCella.Offset(1, 0)
Wend
Set lUltimaCella = lUltimaCella.Offset(-1, lNumColonne - 1)
 
ActiveWorkbook.Names.Add Name:="ERGROSS", _
    RefersTo:="='" & ActiveSheet.Name & "'!$A$2:" & lUltimaCella.Address
 
End Sub

 

[tosti]

When running the script for Col A to Col D and Col F, we also require to change the named range. For example for ERGROSS to ERPeriod2

 

[tosti]

I would also like to set the range Col A to Col D and Col G.
Any advice?

 

[justanotheruser]

I'm not sure how to do this, but I'm interested in this - do you mean for the whole column or some clever code to just do it to the last row where there is data?

 

[justanotheruser]

I don't know if this is too simple, but what about changing the range from A1:e1 to A1:D1:F1 ??

 

[justanotheruser]

Have you had a look at http://www.ozgrid.com/Excel/advanced-dynamic-ranges.htm - I don't know if you can just use this offset code for your dynamic range - I hope one of the geniuses here can help you!