How do I return which row the active cell is in?

Before I start, thanks to all the people who've already helped me on
this board. Solely because of you, I've managed to complete 10% of a
simple but large project (2 applications so far). I've hunted the
boards now for a couple of hours and haven't found the answer to a very
simple issue. What I want to do is this:

I have a worksheet with data already on it. I want to cut and paste
data from another worksheet. I need to identify the first empty row at
the end of the data and return its row number. I tried the following.

Sub test()
    Dim lRowNum As Long

    Range("A65536").End(xlUp).Offset(1, 0).Activate
    lRowNum = ActiveCell.Row
    MsgBox "lRowNum"
End Sub

MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
I get my row number?

Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
smell. Why didn't something crash burn and die at lRowNum =
ActiveCell.Row?

Thanks now for any replies.

Yours, again, in frustrated confusion,

Terry R.

0
9/21/2005 7:27:09 AM
excel 39879 articles. 2 followers. Follow

20 Replies
530 Views

Similar Articles

[PageSpeed] 3

"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message 
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>    Dim lRowNum As Long
>
>    Range("A65536").End(xlUp).Offset(1, 0).Activate
>    lRowNum = ActiveCell.Row
>    MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.

Try MsgBox ActiveCell.Address

Bruno


0
9/21/2005 9:29:15 AM
"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message 
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>    Dim lRowNum As Long
>
>    Range("A65536").End(xlUp).Offset(1, 0).Activate
>    lRowNum = ActiveCell.Row
>    MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.

Forget my previous message, I misunderstood your question.
You write MsgBox "lRowNumber"; why those ""?
MsgBox lRowNum should work, or simply MsgBox ActiveCell.Row.

Bruno



0
9/21/2005 9:34:58 AM
You are displaying a text string, not the variable lRowNum. It displays that
text for me, not OK.

You don't need to activate the last row, just use

    Dim lRowNum As Long

    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    MsgBox lRowNum


-- 
 HTH

Bob Phillips

"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>     Dim lRowNum As Long
>
>     Range("A65536").End(xlUp).Offset(1, 0).Activate
>     lRowNum = ActiveCell.Row
>     MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.
>


0
bob.phillips1 (6510)
9/21/2005 9:52:28 AM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:OERj1IpvFHA.1256@TK2MSFTNGP09.phx.gbl...

> You are displaying a text string, not the variable lRowNum. It displays 
> that
> text for me, not OK.
>
> You don't need to activate the last row, just use
>
>    Dim lRowNum As Long
>
>    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
>    MsgBox lRowNum

You don't even need three lines of code:

MsgBox Range("A" & Rows.Count).End(xlUp) + 1

Bruno 


0
9/21/2005 12:21:29 PM
"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>     Dim lRowNum As Long
>
>     Range("A65536").End(xlUp).Offset(1, 0).Activate
>     lRowNum = ActiveCell.Row
>     MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.
>

your close, but change to:  IRowNum = Range("A65536").End(xlUp).Offset(1,
0).Row




0
Jefgorbach (42)
9/21/2005 12:23:49 PM
Maybe...

MsgBox Range("A" & Rows.Count).End(xlUp).row + 1



Bruno Campanini wrote:
> 
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OERj1IpvFHA.1256@TK2MSFTNGP09.phx.gbl...
> 
> > You are displaying a text string, not the variable lRowNum. It displays
> > that
> > text for me, not OK.
> >
> > You don't need to activate the last row, just use
> >
> >    Dim lRowNum As Long
> >
> >    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >    MsgBox lRowNum
> 
> You don't even need three lines of code:
> 
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
> 
> Bruno

-- 

Dave Peterson
0
petersod (12004)
9/21/2005 12:25:40 PM
"Jef Gorbach" <Jefgorbach@aol.com> wrote in message 
news:u3zildqvFHA.2008@TK2MSFTNGP10.phx.gbl...

> your close, but change to:  IRowNum = Range("A65536").End(xlUp).Offset(1,
> 0).Row

You'r right, the shorter:
MsgBox [A65536].End(xlUp) + 1

Bruno 


0
9/21/2005 12:32:08 PM
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:43315144.ABFC777@verizonXSPAM.net...
> Maybe...
>
> MsgBox Range("A" & Rows.Count).End(xlUp).row + 1

If you prefer, but it also works without .Row:
MsgBox Range("A" & Rows.Count).End(xlUp) + 1

Bruno 


0
9/21/2005 12:35:23 PM
It depends on what's in that cell.

I'm betting you just put 1, 2, 3, .... in your test data.

Bruno Campanini wrote:
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43315144.ABFC777@verizonXSPAM.net...
> > Maybe...
> >
> > MsgBox Range("A" & Rows.Count).End(xlUp).row + 1
> 
> If you prefer, but it also works without .Row:
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
> 
> Bruno

-- 

Dave Peterson
0
petersod (12004)
9/21/2005 12:51:45 PM
Bruno,

Your way is wrong.  Dave's way is correct, and not just "a preference". The default property of a 
range object is .Value, so your code will return the value of the last cell + 1.  If that value is a 
number, you will get that number +1, not the row number +1.  If the value is a string, you will get 
an error.

Since you want the row number, use .Row

HTH,
Bernie
MS Excel MVP


"Bruno Campanini" <bruno.campanini@tin.it> wrote in message 
news:uA93xjqvFHA.2292@TK2MSFTNGP12.phx.gbl...
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
> news:43315144.ABFC777@verizonXSPAM.net...
>> Maybe...
>>
>> MsgBox Range("A" & Rows.Count).End(xlUp).row + 1
>
> If you prefer, but it also works without .Row:
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno
> 


0
Bernie
9/21/2005 12:52:03 PM
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message 
news:uOFUEtqvFHA.3500@TK2MSFTNGP09.phx.gbl...
> Bruno,
>
> Your way is wrong.  Dave's way is correct, and not just "a preference". 
> The default property of a range object is .Value, so your code will return 
> the value of the last cell + 1.  If that value is a number, you will get 
> that number +1, not the row number +1.  If the value is a string, you will 
> get an error.
>
> Since you want the row number, use .Row

You are perfectly right!
I tested in a column terminating with the corresponding row number...

Arghhhh

Bruno 


0
9/21/2005 2:05:33 PM
You should certainly not omit the

    Dim lRowNum As Long

very poor coding practice

-- 
 HTH

Bob Phillips

"Bruno Campanini" <bruno.campanini@tin.it> wrote in message
news:OP$FCcqvFHA.2960@tk2msftngp13.phx.gbl...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OERj1IpvFHA.1256@TK2MSFTNGP09.phx.gbl...
>
> > You are displaying a text string, not the variable lRowNum. It displays
> > that
> > text for me, not OK.
> >
> > You don't need to activate the last row, just use
> >
> >    Dim lRowNum As Long
> >
> >    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >    MsgBox lRowNum
>
> You don't even need three lines of code:
>
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno
>
>


0
bob.phillips1 (6510)
9/21/2005 3:21:46 PM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:%23pLI3AsvFHA.3864@TK2MSFTNGP12.phx.gbl...

> You should certainly not omit the
>
>    Dim lRowNum As Long
>
> very poor coding practice

I've NEVER used lRowNum on my examples,
only MsgBox Range...
Or, if I did, it was only with reference to Terry's code.

Is this a good code:
----------------------------
Dim lRowNum As Long

    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    MsgBox lRowNum
------------------------------

compared with this one?
-----------------------------
MsgBox Range("A" & Rows.Count).End(xlUp).Row + 1
-----------------------------

Your code reveals a complete omologation to the
mass of fantasyless programmers:
Blank lines everywhere.
Indentention when there is no need for indent.
Code redundancy (Offset can be replaced by +1 at
the end of line)

And - I suppose -
Dim ... as Variant
Option Base 0
Range("A1").Value
Application.WorksheetFunction
etc.

Art of communication has much more to deal with
quality than with quantity.
Software vendors don't.

Well Bob, since now on I'd like to be very sympathetic with you.
Hopefully, would you be the same?

Bruno 


0
9/21/2005 4:21:31 PM
Bruno,

Thank you. Both worked. Have a good one.

Terry R.

0
9/21/2005 11:58:37 PM
Bob,

Thank you for your response. I really like the way that you can return
the number of the row without actually having to go there. So I started
to experiment with your code and got a weird anomaly. I cut and pasted
your exact code into a small test subroutine.

I put the following data into a new worksheet:
The number 1 into cell A1, the number 2 into cell B2, and the number
three into cell C3.

When I ran the code "lRowNum = Range("A" &
Rows.Count).End(xlUp).Offset(1, 0).Row" for column A, I got the answer
"2", as you would expect. Similarly, for "lRowNum = Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row I got 3, and for "lRowNum =
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row" I got 4.

But when I ran "lRowNum = Range("D" & Rows.Count).End(xlUp).Offset(1,
0).Row", with nothing in column D, I got back 2. The same was true for
every other column with nothing in it.

Do you think this is because of the "Offset(1, 0)" factor? So, to use
this reliably, even for columns which have nothing in them, would you
require further code?

Still slightly confused, yours sincerely

Terry R.

0
9/22/2005 12:25:24 AM
Bob's code is like selecting the last cell in column D.  Then hitting the End
key and up arrow (to find that last used cell.  If column D is empty, end
followed by uparrow gets you to D1.  .offset(1,0) will drop you down 1.

You could use a minor variation.

Dim NextCell as Range
dim NextRow as long
with worksheets("sheet1")
  set Nextcell = .cells(.rows.count,"D").end(xlup)
  if isempty(Nextcell) then
      'do nothing, don't change anything
  else
     set nextcell = nextcell.offset(1,0)
  end if
  nextrow = nextcell.row  'if you need it
end with

=========
But now you may have another problem--what happens if there's data in
D65536????)

<vbg>
  

Cloudfall wrote:
> 
> Bob,
> 
> Thank you for your response. I really like the way that you can return
> the number of the row without actually having to go there. So I started
> to experiment with your code and got a weird anomaly. I cut and pasted
> your exact code into a small test subroutine.
> 
> I put the following data into a new worksheet:
> The number 1 into cell A1, the number 2 into cell B2, and the number
> three into cell C3.
> 
> When I ran the code "lRowNum = Range("A" &
> Rows.Count).End(xlUp).Offset(1, 0).Row" for column A, I got the answer
> "2", as you would expect. Similarly, for "lRowNum = Range("B" &
> Rows.Count).End(xlUp).Offset(1, 0).Row I got 3, and for "lRowNum =
> Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row" I got 4.
> 
> But when I ran "lRowNum = Range("D" & Rows.Count).End(xlUp).Offset(1,
> 0).Row", with nothing in column D, I got back 2. The same was true for
> every other column with nothing in it.
> 
> Do you think this is because of the "Offset(1, 0)" factor? So, to use
> this reliably, even for columns which have nothing in them, would you
> require further code?
> 
> Still slightly confused, yours sincerely
> 
> Terry R.

-- 

Dave Peterson
0
petersod (12004)
9/22/2005 12:38:07 AM
Dave,

It worked. Thank you for the warning re data in D65536. You once gave
me a warning in another post:
"If you have formulas in the range, this will keep them as
formulas--your code:
selection.value = selection.value
would cause damage if you're not careful."
Sure enough, I wound up doing that on one occasion. As I was about to
post another question, your warning came back to me and I came up with
a workaround.

Thanks for your help,

Terry R.

0
9/22/2005 1:44:37 AM
Jef,

Thanks, this works great.

Terry R.

0
9/22/2005 1:51:41 AM
Bruno,

This takes the value in the last cell and adds "1" to it.

Thank you for your response,

Terry R.

0
9/22/2005 1:52:08 AM
Glad you got things working.

Cloudfall wrote:
> 
> Dave,
> 
> It worked. Thank you for the warning re data in D65536. You once gave
> me a warning in another post:
> "If you have formulas in the range, this will keep them as
> formulas--your code:
> selection.value = selection.value
> would cause damage if you're not careful."
> Sure enough, I wound up doing that on one occasion. As I was about to
> post another question, your warning came back to me and I came up with
> a workaround.
> 
> Thanks for your help,
> 
> Terry R.

-- 

Dave Peterson
0
petersod (12004)
9/22/2005 2:06:50 AM
Reply:

Similar Artilces:

Dynamic unlock a cell (under condition)
Hello guys, lets see if anyone could help me out on how to do this: I have a sheet protected with a password, the reason of this (you know) is that there are some cells that are locked (to not see their formulas or 'cause I don't want them to be changed under any circustances). This is working fine, but now I have the need of some cells (two ranges actually, these are E7:E56 & N7:N56) that they come locked by default but under certain conditions I would need them to get unlocked. Taking an E7 as example, let's say that depending on what we have in B7 this E7 will chang...

multiply a row by a certain number?
i am having trouble with excel i want to multiply this row by 1.4 and make it appear next to it if anyone can help thank you if you mean multiply each item in a column then this will work. Modify to suit Sub multiplyall() For Each c In Range("c7:c10") c.Offset(, 1) = c * 1.4 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "multiply a row by a certain number?" <multiply a row by a certain number?@discussions.microsoft.com> wrote in message news:C84A6F67-03CD-4902-9760-36051A179831@microsoft.com... > i am having trouble with excel i want to multipl...

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

How do I count the number of cells
I have several dozens columns and 52 rows containing Data. All the columns are labled Odd or Even In the final (right most) Column I want to count the number of times that that coulmn has data in it The last row belows shows what the answer should be Total Total Odd Even Odd Even Odd Even Odd 5 1 2 ????? ????? 1 7 6 3 ????? ????? 5 1 2 ????? ????? 1 2 9 3 1 3 PLease help =COUNTA() - will count the number of non blank cells. In the brackets place the range name (for example A1:A2). Please hit Yes if my comments have helped. Thanks. &...

Convert VLOOKUP to absolute cell reference
My spreadsheet has a VLOOKUP through 44,000 rows that only needed to be performed once. The relevant data is in the VLOOKUP cells as I want them and the lookup values will never change. As long as I keep the other worksheet intact, can I convert the VLOOKUP formulas to absolute references? My spreadsheet is running very slow with the VLOOKUP in place, so I'm trying to speed things up. Would this idea work? Hi Rich, You can try to convert all the formulas as values by using copy and paste special values. also you can try to go to options calculation and change the options to manu...

Comments in Cells
Is there a way to show the comments against a cell when printing a worksheet out? Regards Ric You can print the comments as they display on the worksheet, or at the end of the worksheet. There are instructions in Excel's Help, and here: http://www.contextures.com/xlcomments01.html#Printing Ric wrote: > Is there a way to show the comments against a cell when printing a worksheet > out? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:407D1E25.407...

Remove Cell Auto-Format (General) on Paste / Replace
Good morning all, Just curious if this is able to be done. I have a series o spreadsheets, all of them do different things, but the common factor i that I paste claim numbers into them. The problem is, Excel seems t think our claim numbers are dates. (Example. 2146/04 or 04/262 (different formats for different sections). Now, I have formatted AL cells on these spreadsheets as text. (Ctrl-A, Format-Cells-Text However, when pasting, Excel overrides whatever you've got, and choose it's own way to paste data. The other problem is with Find-Replace or Find-Replace All. A lot o the time, ...

Format cells unavailable
I am assisting a customer running Word 2000 on a Windows 2000 system. He has a spread sheet that will not allow him to do a format > cells on the header row and a few rows below. We can select the colum and this option works but nothing happens when using this option for an individual cell. I don't think these items are protected, is there any way I can be sure? I tried selecting all of the columns and unlocked the cells and this did not help. Can anyone think of any reason why format cells would not be available on this sheet? TIA George Tools, Protection. If the sheet's ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Merge/consolidate text cells based on unique keys ?
Hi all. I hope someone can help me out. I have an Excel worksheet with 2 columns: 'Client #' and 'Invoice #'. Every time the accounting dept. generates an invoice, a new row is added in this worksheet. Obviously this is chronological not per Client #. But for the sake of simplicity, let's assume the worksheet is already sorted by Client #, like so: A B Client # Invoice # 231 5929 231 4358 231 2185 231 6234 464 1166 464 1264 464 3432 464 1720 464 9747 791 1133 791 4930 791 5496 791 6291 989 8681 989 3023 989 7935 989 8809 989 8873 My goal is to achieve...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Use tiltle in a cell
Is there a way to use the title of the spreadsheet as a cell value? I asked this before in another topic, but there was no reply. My sheet get's it's title from a database and is variable. So if i can use the title i can strip several characters from the title and use this. So I want to retrieve the title of the spreadsheet and use this in a cell. Thanks! The name of a worksheet can be extracted to a cell with: =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from em...

How configure exchange to convert all incoming emails in CRM activities?
Hi everybody, When I go to tools/options/activities and click in the=20 option "converting all incoming emails" a dialog inform=20 who is necessary server additional configuration. Well, I=20 am looking for informations about this configuration in=20 the CRM documentation but with no sucess. Can somebody=20 help me? * I am using the portuguese version, so I don't know if=20 the names who I used are rigth. Thank you for pay attention and sorry my bad english. :) []'s Vin=EDcius Pitta Lima de Ara=FAjo The instructions are in the implementation guide. =20 Basically, you will ...

Linking Dialog box content with cells in "regular" sheet
How to link data in Edit box (from Dialog caption) with exact cells in "regulal" Sheets? Or is there another way to link exact content from Dialog sheet to normal sheet? For example, if I have number 200 in Dialog, I need that exact number 200 on another ("regular") sheet to preform calculations with it. thnx ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

Wordwrap and AutoFit Rows
Hi All! Question: Is there a maximum number of characters that a single cell will accept? I have this well with a really really long paragraph in it and it's not showing the last few words in the sentence... Also, is there a way to format the sheet so when I change the column width the autofit row function will automatically update the contents of the cells? ... As it is now, when I make the column wider, I'm having to click into each cell and hit enter so that it adjusts the row height... Thanks in advance! LavaDude... Try adding alt-enters every 80-100 characters. Yo...

Copy Cells
Hi all I have a spreadsheet which has lots of calls logged on it. Each call is logged by a person. I have a tab for each person. What i need to do is each persons tab needs to pick up the information for that person (pick it out of the main sheet which has everyones call on it). So any cell in column 'A' with the name 'Joes Bloggs' needs to copy accross to tab 'Joe Bloggs' along with the rest of the information on that line. Hope this makes sense, if you dont understand reply with a question. Thanks in advance Darren --- Message posted from http://www.ExcelForum...

Deleting rows 05-03-10
I'm trying to figure out if I can set up a macro to delete rows if they do not contain anything in specific columns. For example, I have product codes in column A and totals for on-hand and ordered numbers in columns B and C. If some rows do not have on-hand or ordered numbers I want to delete those rows. Any ideas? Start here http://www.rondebruin.nl/delete.htm For example in the first macro use Change the range in this line that you want to test If Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then .Rows(Lrow).Delete Sub Loop_Example() ...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- 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.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Replace empty cell with cell on right
Hi all I wish I could write macros!! I have a spreadsheet of names and addresses (name, add1, add2, add3, add4, add5). I want to mailmerge them but some add2s are empty, because whoever typed the data in put the next line in add3, and some add3s are empty because they used add4, and some add4s are empty etc etc This means that the mailmerge (into Publisher - yuk!) puts blank lines into the address. I would like (please!) a macro that, if I select the range add1-add5, will squash the details to the left - so all of the empty cells are on the right! Thanks. -- Andy. Good news - you don&#...

Selecting Rows for Copying
Hi, I've been happily using a macro that has been copying and pasting data for me. However, I have recently encountered an issue where the sheets I am copying my data from have merged cells. As far as I can tell, Selection.Rows.Count will not work as desired when some columns are merged, but others are not. Basically it is counting what may be 100 rows of data as one row, due to the cell merging. ' select all lines except title Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select Is there another way to select all of the data, excluding the header row, whe...

Storing current dates and system username on a cell
I need a code either a formula or better still a sub procedure o function for storing a current date value which cannot alter after tha date and the username(from the system) on a cell automatically onl after the user has clicked the saved button. For example Date (to be automatated) :username(to be automatated) 08/07/2004 : Jo freazer 09/07/2004 : Kelly hamburger 10/07/2004 : Tai Azi 11/07/2004 : Kelly hamburger Thank yo -- Message posted from http://www.ExcelF...

How do I cancel column & row highlight in Excel?
As I move the cursor around an Excel sheet, the column letter and row number are highlighted which prevents me from sorting the data in the sheet. Any ideas as to how cancel this in order that I am able to sort the whole text ?? ...