Define a name in VBA

  • Thread starter Thread starter bijan
  • Start date Start date
B

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
 
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 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
Daniel.C said:
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
Click to expand...
Click to expand...
 
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
Daniel.C said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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
 
Back
Top