If you want to create name ranges using vba. Snapshot below-
Here is the code-
' To Create a single name range
Sub create_single_name_range()
Dim rng As Range
' change the range here
Set rng = Sheets(1).Range(" c2:c235")
' create name range with "Jan"
ActiveWorkbook.Names.Add Name:="Jan", RefersTo:=rng
End Sub
'to create mulitple name ranges using for loop
' this macro will create name ranges with month or cell values in row 1
Sub Creating_Name_ranges()
Dim rng As Range
Dim I As Integer
For I = 3 To 8
' Sheets(1).Cells(2, I).Address will give first c2, d2, e2, f2 ,etc
'Sheets(1).Cells(Range("a65356").End(xlUp).Row, I).Address will give c235, d235,e235, etc
Set rng = Sheets(1).Range(Sheets(1).Cells(2, I).Address & ":" & Sheets(1).Cells(Range("a65356").End(xlUp).Row, I).Address)
' create name range with name value of cell c1, d1,e1, etc.
ActiveWorkbook.Names.Add Name:=Sheets(1).Cells(1, I).Value, RefersTo:=rng
Next
End Sub
Download Excel Workbook
Subscribe to:
Post Comments (Atom)
Import data from SQL
Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...
-
If you want to add a new menu on mouse right click "Workbook Navigation showing you the list of all open workbooks and worksheets in ea...
-
Macro to Export Range in Json Format Option Explicit Sub export_in_json_format() Dim fs As Object Dim jsonfile Dim rangetoex...
No comments:
Post a Comment