Define a name in VBA

[bijan]

Hi All,
I need a VBA code to select a non-active sheet(TEST) and then detect
non-blank area to define a name(GI) to it.
Thanks in advance
Bijan

 

[Daniel.C]

HI.
Try :

Sub test()
Dim LCol As Integer, RCol As Integer, TRow As Long, BRow As Long
Sheets("TEST").Select
LCol = Cells.Find("*").Column
RCol = Cells.Find("*", [IV1], , , xlByColumns, xlPrevious).Column
TRow = Cells.Find("*").Row
BRow = Cells.Find("*", [A65000], , , xlByRows, xlPrevious).Row
ActiveWorkbook.Names.Add "GI", _
RefersTo:=ActiveSheet.Range(Cells(TRow, LCol), Cells(BRow, RCol))
End Sub

HTH
Daniel

 

[Bob Phillips]

Worksheets("TEST").Activate
Activesheet.UsedRange.Name = "GI"

 

[Daniel.C]

AFAIK, "usedrange" includes blank cells with non standard formatting.
Daniel

 

[bijan]

Hi daniel,
Thanks for your answer, your code is working but with wrong range, it Named
range"B2:E23" but the sheet test was started from "A1:E23"
Thanks
Bijan

HI.
Try :

Sub test()
Dim LCol As Integer, RCol As Integer, TRow As Long, BRow As Long
Sheets("TEST").Select
LCol = Cells.Find("*").Column
RCol = Cells.Find("*", [IV1], , , xlByColumns, xlPrevious).Column
TRow = Cells.Find("*").Row
BRow = Cells.Find("*", [A65000], , , xlByRows, xlPrevious).Row
ActiveWorkbook.Names.Add "GI", _
RefersTo:=ActiveSheet.Range(Cells(TRow, LCol), Cells(BRow, RCol))
End Sub

HTH
Daniel

Hi All,
I need a VBA code to select a non-active sheet(TEST) and then detect
non-blank area to define a name(GI) to it.
Thanks in advance
Bijan

 

[Daniel.C]

Sorry :

Sub test()
Dim LCol As Integer, RCol As Integer, TRow As Long, BRow As Long
Sheets("TEST").Select
LCol = Cells.Find("*", , , , xlByColumns).Column
RCol = Cells.Find("*", [IV1], , , xlByColumns, xlPrevious).Column
TRow = Cells.Find("*", , , , xlByRows).Row
BRow = Cells.Find("*", [A65000], , , xlByRows, xlPrevious).Row
ActiveWorkbook.Names.Add "GI", _
RefersTo:=ActiveSheet.Range(Cells(TRow, LCol), Cells(BRow, RCol))
End Sub

Daniel

Hi daniel,
Thanks for your answer, your code is working but with wrong range, it Named
range"B2:E23" but the sheet test was started from "A1:E23"
Thanks
Bijan

HI.
Try :

Sub test()
Dim LCol As Integer, RCol As Integer, TRow As Long, BRow As Long
Sheets("TEST").Select
LCol = Cells.Find("*").Column
RCol = Cells.Find("*", [IV1], , , xlByColumns, xlPrevious).Column
TRow = Cells.Find("*").Row
BRow = Cells.Find("*", [A65000], , , xlByRows, xlPrevious).Row
ActiveWorkbook.Names.Add "GI", _
RefersTo:=ActiveSheet.Range(Cells(TRow, LCol), Cells(BRow, RCol))
End Sub

HTH
Daniel

Hi All,
I need a VBA code to select a non-active sheet(TEST) and then detect
non-blank area to define a name(GI) to it.
Thanks in advance
Bijan

 

[Bob Phillips]

I interpreted non-blank area as usedrange, not as the area with no blank
cells within it. The area isn't blank, individual cells may well be, but not
the whole.

 

[Shane Devenshire]

Hi,

You don't need to activate the sheet to name a range on it, so your code
could be 1 line:

Sheets("Sheet1").UsedRange.Name = "Data"

If this helps, please click the Yes button

cheers,
Shane