Define a name in VBA

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
0
bijan (31)
11/12/2008 10:13:01 AM
excel 39879 articles. 2 followers. Follow

7 Replies
580 Views

Similar Articles

[PageSpeed] 51

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


0
11/12/2008 10:57:11 AM
Worksheets("TEST").Activate
Activesheet.UsedRange.Name = "GI"

-- 
__________________________________
HTH

Bob

"bijan" <bijan@discussions.microsoft.com> wrote in message 
news:4DB8B172-AC14-4EDA-A84A-1D26EE8FCA6C@microsoft.com...
> 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 


0
BobNGs (423)
11/12/2008 11:52:43 AM
AFAIK, "usedrange" includes blank cells with non standard formatting.
Daniel

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


0
11/12/2008 12:37:38 PM
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" wrote:

> 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
> 
> 
> 
0
bijan (31)
11/12/2008 2:04:23 PM
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" wrote:
>
>> 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
>> 
>> 
>>


0
11/12/2008 2:16:03 PM
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.

-- 
__________________________________
HTH

Bob

"Daniel.C" <dcolardelleZZZ@free.fr> wrote in message 
news:OSGDuNMRJHA.1908@TK2MSFTNGP04.phx.gbl...
> AFAIK, "usedrange" includes blank cells with non standard formatting.
> Daniel
>
>> Worksheets("TEST").Activate
>> Activesheet.UsedRange.Name = "GI"
>
> 


0
BobNGs (423)
11/12/2008 2:29:44 PM
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


"Bob Phillips" wrote:

> Worksheets("TEST").Activate
> Activesheet.UsedRange.Name = "GI"
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "bijan" <bijan@discussions.microsoft.com> wrote in message 
> news:4DB8B172-AC14-4EDA-A84A-1D26EE8FCA6C@microsoft.com...
> > 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 
> 
> 
> 
0
11/12/2008 3:57:00 PM
Reply:

Similar Artilces:

Automatic control names
I feel stupid asking this question, but I can't find any previous posts about it. Is it possibe to set Access 2007 so that the forms wizard will name the controls properly? The default is to name the control with the field name of the data source, but it ignores the proper naming conventions (or at least what I was taught was the convention). A textbox should be named "txtFirstName", but instead it just names it "FirstName", which then requires us to have to go through and manually rename all the controls. I suppose that a VBA routine can be written, but af...

VBA macro help
Excel Chiefs, I'm a new entrant to the world of VBA.I have a large excel workboo litered with tons of macros. However I need a macro that will logicall test these entries and return a value "ENTRY Valid", "warning check f file", etc in cell F24 how will i do this? Also if i want the value a a msg box pop up instead of in cell F24 what additional code will require?Below is the Logical test : IF(AND(I13=I15, (ABS((K15-K13)/K15)< 0.05),$M$13=$M$15),"ENTR Valid",IF(OR(M13/O13>1.9),"","WARNING check FI file !")) Any help will be since...

VBA copy 'corrupts' font selection (XL2003)
I have raw data on Sheet3, with the entire sheet formatted in Times New Roman. I have a form on Sheet1 where I copy sections of data from Sheet3 in a desired order. I also took Sheet1, selected the whole sheet, and formatted in TNR Then I run the code below; all data that is copied over from Sheet3 keeps getting pasted in Arial format! Any ideas on what would cause this? I'd prefer to fix the root cause, rather than "band-aid" it by just forcing a reapplication of the desired font after all records are pasted. Thank you, Keith Private Sub Worksheet_Chang...

#name? error
Whats wrong with this expression in text box on main form reading value of field [SumOfEmp_Act_Prod_Hrs] in subform named 'qry_Emp_TotalHrsWorked subform' - it is getting #name? error =IIf([Forms]![qry_Emp_TotalHrsWorked subform]![SumOfEmp_Act_Prod_Hrs]>1920,"EMP HOURS ARE GREATER THAN 1920","Emp Product Hours Less Than 1920") Are hours in units of time or text? If text your 1920's must be "1920" -- Milton Purdy ACCESS State of Arkansas "Steve Stad" wrote: > Whats wrong with this expression in text box on main...

Dynamic Range Naming for VBA
I've searched through the many threads that deal with naming ranges but have not come a solution to my problem, thanks for your patience with another VBA newbie. In the code below, I correctly select the range which I would like to name "MyRange" What must I add to the code to name this selected range? Worksheets("TempSheet").Range("A2:A" & Sheets.Count - 4).Select Thanks for your help. mrtoemoss@hotmail.com wrote: > I've searched through the many threads that deal with naming ranges but have > not > come a solution to my problem, t...

Need macro to get machine name
Is there any way to get the machine name with an Excel macro? I'd even settle for a simple DOS script that I could launch from the macro. Thanks, Barry msgbox environ("ComputerName") -- HTH Bob Phillips "BCS" <bswedeen@tayloroil.com> wrote in message news:g_0le.635$_z6.78132@twister.southeast.rr.com... > Is there any way to get the machine name with an Excel macro? I'd even > settle for a simple DOS script that I could launch from the macro. > > Thanks, > > Barry > > Thanks Bob! Works like a charm! Barry "Bob Ph...

The ability to set the naming convention for EFT files.
Some banks have limits on the length of the file name that can be submitted for EFT. With the new naming convention the file names are all to long. Can you devise a method for setting the namin converntion. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www....

Show series name on the chart
I want to show the name of the data series on the chart - but only once. If I check "Series name" in the "Format data series" "Data Labels" tab - then it shows at every data point - I only want it at e.g. the last. How is that done? Hi, To do this manually first select the series and then select the individual point. Now apply data labels. With VBA code have a look at Jon Peltier's page http://peltiertech.com/Excel/Charts/LabelLastPoint.html With dummy data series see http://www.andypope.info/charts/Labellast.htm Cheers Andy -- Andy Pope, Microsof...

Does the MS Excel viewer run VBA?
Hi all, I was wondering if the "free" MS Excel viewer also runs any associated VBA code or at least simple macros?? TIA, Miki Miki, No, the free Excel viewer does not run any sort of VBA code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Michelle" <miki@spam_me.net> wrote in message news:%0SUb.240917$na.397525@attbi_s04... > Hi all, > > I was wondering if the "free" MS Excel viewer also runs any associated > VBA code or at least simple macros?? > > TIA, > > Miki NO ...

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

Run vba code on initialisation?
How can I get a macro (vba module) to run automatically when a workbook/worksheet is loaded? Is there an Initialisation event? TIA Chris Found the answer. Use the Workbook Open event. cheers Chris "Chris Watts" <ng@ctwatts.plus.com> wrote in message news:uxG5Y42HKHA.4376@TK2MSFTNGP03.phx.gbl... > How can I get a macro (vba module) to run automatically when a > workbook/worksheet is loaded? Is there an Initialisation event? > > TIA > Chris ...

convert repeated sheet 1 names to once in sheet2
i have a work sheet contained name with score, in that sheet names are repeating scores also repeated but values are change( ex: a=10, b= 6, c= 2, a= 18, b= 1, c= 8, a =1) in the sheet 2 i want only once of the name with total value what i do plesase help me Naanishiva - It looks like you need a pivot table. Use the pivot table wizard to do this - you will to select the data area of the worksheet including the column titles (name, score), and then put the results in the second worksheet. Use the Layout button in the wizard to put the name in the Rows section, and the ...

Display File As field insted of Full Name
I'm using Outllok 2000, I have stored my contacts on a contact folder which is shown as an e-mail Address Book. The field Full Name (ex John Smith) and the field File As (ex Johnny) are different beacause I want to use the field File As as a more friendly to use field. I have chosen for the names in the Address book to be sorted by the File As field. When I insert a name from that list as a message recipient the name which I have chosen is shown (ex In the "To" field an underlined "Johnny" is shown). But when I sent the mail the Full Name (John Smith) appeares on the ...

Attaching Help File to a User Defined Function
Hi, I wrote some functions in VBA for Excel and I would like to attach help file to each function. One solution was to attach the help file in the VBA project properties but I am allowed to attach just one file per project. As I have 4 functions in that project (they all have to reamain in the same VB project)... I have to find another solution. What would the solution be? Tks. Maca. :confused -- mac ----------------------------------------------------------------------- maca's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2489 View this thread: http://w...

trying to get sheet 1 to recognize and move student names to pg 2
Sheet 1 contains students names in column A in ascending order, columns b thru k are columns which grading numbers of either 1 point or 2 points, column l is the total points of colums b thru k. Sheet 2 contains 5 columns , column a is 17 to 20 points, column b is 13 to 16 points, column c is 9 to 12 points, 5 to 8 points, column d is 5 to 8 points and column e is 0 to 4 points. I need sheet 1 column L to recognize sheet 2 and move the students name from column A in that row to the graph on sheet 2 according to the point system Is this one-time operation? You have only 5 c...

Re-defining default save location for attachments
Can someone please tell me how I can change the default folder that attachments that come in an e-mail are saved? I know that they are currently going to the Temporary Internet Files\OKL* Pat <anonymous@discussions.microsoft.com> wrote: > Can someone please tell me how I can change the default > folder that attachments that come in an e-mail are saved? > I know that they are currently going to the Temporary > Internet Files\OKL* Use Google Groups Search on this newsgroup. It has been answered several times. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B...

Sorry for X-Post but does anyone know where IMAPI_E_DEVICE_NOT_PRESENT is defined?
It isn't defined in Imapi.h and I've done a complete search and found it defined no place... WTH Hmmm... Documented incorrectly. IMAPI_E_MEDIUM_NOTPRESENT is the define... WTH "WTH" <ih8spam@spamtrap.com> wrote in message news:uIyPU053DHA.2112@TK2MSFTNGP10.phx.gbl... > It isn't defined in Imapi.h and I've done a complete search and found it > defined no place... > > WTH > > You should be able to find this in the imapierror.h file. [ You probably need the latest platform SDK ] -- Cheers Check Abdoul [VC++ MVP] -----...

VBA and Excel Viewer
Does anyone know why the Excel Viewer doesn't support VBA? Hi just a (business) decision by MS. And also probably due to the effort to implement a 'VIEWER' which can also execute code -- Regards Frank Kabel Frankfurt, Germany "Robert Kent" <robertkent@yahooie.coma> schrieb im Newsbeitrag news:Oi$xOACcEHA.1144@TK2MSFTNGP11.phx.gbl... > Does anyone know why the Excel Viewer doesn't support VBA? > > > ...

[VBA] Dynamic target stock
Hi, for a case-study in my university I need to create an excel-file. But I have some problems with a variable which should be dynamic. Below you can find a link to an example with further descriptions. I need to calculate the target stock in Sheet 2 via a VBA code because target stock is dynamic and dependant on shortage, capacity and stocks. Hope you can help me http://www.2shared.com/file/9489655/610940d1/dynamic_target_stock.html What specifically are you having issues with. I typically don't download files from this site, but if there are questions you have, we ...

merged print names 4 up post card
USING PUBLISHER 2000 - The merged print dialog box does not provide a selection for print side 1, print side 2 separately (instead printing side 1,2 side 1,2 etc,) I desire to print the clients <<first name>> (as in dear <<first name>>) on side 1, and mailing address <<first name>>,<< last name>>, <<address>>, etc. on side 2. I did follow the site instructions to create two separate files - but names will not line up on the 4 up post card. I do not have a duplexing unit on my HP8550 printer, so I must flip the stack over and ru...

Cannot resolve names in Outlook 2000 running on a Exch 2000 Server
I just moved to an exchange 2000 server from a MS SBS running Exchange 5.5. This wasn't a migration but a move using PST file since my environment is so small. All seems well but we lost the ability to automatically reslove names when creating new emails, mainly in the To field. I have checked the end user setting and they are set to do automatic name checking in the Outlook email options. If anyopne can please help I would greatly appreciate it. The move went great but leave it to the users to think this is a major issue :-) I'm wondering if this is an Exchange issue sine i...

How SUM come here? {=SUM((Rng1="Car")*(Rng2="names")*(Profits))}
When looking at SUM's syntax it says (number1, number2 ...). How does one figure out that even these type of statements are possible? Does the Manual list all such possibilities? Try =SUMPRODUCT(--(Rng1="Car"),--(Rng2="names"),Profits) There are no manuals in Excel anymore so I assume you mean help, I believe 97 was the last one with a manual It was also the last one with decent help -- Regards, Peo Sjoblom "dindigul" <padhye.m@gmail.com> wrote in message news:%237sV8qllHHA.1820@TK2MSFTNGP04.phx.gbl... > When looking at SUM's synta...

ANSI sequence in concencated vba
Hi I have 2 tables Groups and Clients Groups = GroupID, etc, etc Clients = ClientID, GroupID, BookRef, etc,etc GroupID and ClientID are auto numbers and BookRef is text. I have a form that sets the value of the BookRef like this Private Sub Form_BeforeInsert(Cancel As Integer) If Me.NewRecord Then Me.ClientID = Nz(DMax("[ClientID]", "tblClients", "[GroupID] = " & Me.GroupID)) + 1 Me.BookRef = Me.GroupID & "-" & Me.ClientID & "-" & Right(Str(Year([Forms]![frmGroups]![TripStart])), 2) End If End Sub Trip start is a ...

inputing data using vba #2
hi can any body tell me how to make an input box with four option buttons the first three are data file's i already have and the last a empty line with a browse button to search for a file on my hard disc and then an execute button below that will input the relevant data thanks guys ...

Change Domain Name
How do you change the domain name for the entire domain? You mean the e-mail domain Exchange is handling? What version of Exchange, if so? Calvin wrote: > How do you change the domain name for the entire domain? The problem is that my Domain name is XYZ but i want my exchange domain name to be yzx.com. I'm using Exchange 2003. >-----Original Message----- >You mean the e-mail domain Exchange is handling? What version of Exchange, >if so? > >Calvin wrote: >> How do you change the domain name for the entire domain? > > >. > So I guess we are ta...