Range Question

I would like to be able to grab the row information from the first Do-While
loop (column D) and use it to create a range for the ActiveCell.Copy and
PasteSpecial (column J), instead of using a loop to find once again an empty
value. This works, but I couldn't figure out the code for the range to make
it run faster.  Any help is greatly appreciated.

Thanks,
Mike

Range("D2").Select
    Do While ActiveCell.Value <> Empty
    ActiveCell.Offset(1, 0).Select
    Loop
    For I = 1 To 15
        ActiveCell.Offset(1, 0).EntireRow.Delete
    Next I
    Range("J2").Select
    Do While ActiveCell.Offset(1, -1).Value <> Empty
    ActiveCell.Copy
    ActiveCell.Offset(1, O).PasteSpecial (xlPasteFormulas)
    Loop
    Range("J2").Select


0
none89 (807)
10/2/2003 11:38:39 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
707 Views

Similar Articles

[PageSpeed] 21

The way to get the row of the active cell is:

rw = activecell.row

rw is a variable that stores the row number.
You don't have to loop to get to the first empty cell 
down. Assuming your current celection is a single cell 
somewhere in the target column and above the empty cell 
looked for, all you need to do is:

selection.end(xldown).offset(1,0).select

Alternatively, if you want to select a range expanding 
from your currently selected cell (single) to the last one 
down before an empty cell, you can use:

range(selection, selection.end(xldown)).select

Likewise with xlup, xltoright, xltoleft.

Note: if your current selection is a range rather than a 
single cell, it will still work but select a wider range 
matching your original selection. Actually, this is very 
useful in deleting/inserting entire rows or columns. If 
you select an endtire row and then use the last expression 
above you will select all the rows from the currently 
selected one to the last one before a blank cell in the 
column where your activecell is.

Hope this helps,

Nikos Y.


>-----Original Message-----
>I would like to be able to grab the row information from 
the first Do-While
>loop (column D) and use it to create a range for the 
ActiveCell.Copy and
>PasteSpecial (column J), instead of using a loop to find 
once again an empty
>value. This works, but I couldn't figure out the code for 
the range to make
>it run faster.  Any help is greatly appreciated.
>
>Thanks,
>Mike
>
>Range("D2").Select
>    Do While ActiveCell.Value <> Empty
>    ActiveCell.Offset(1, 0).Select
>    Loop
>    For I = 1 To 15
>        ActiveCell.Offset(1, 0).EntireRow.Delete
>    Next I
>    Range("J2").Select
>    Do While ActiveCell.Offset(1, -1).Value <> Empty
>    ActiveCell.Copy
>    ActiveCell.Offset(1, O).PasteSpecial (xlPasteFormulas)
>    Loop
>    Range("J2").Select
>
>
>.
>
0
nyannaco (9)
10/3/2003 7:22:03 AM
Reply:

Similar Artilces:

Formula to count the cells in a range that have a fill color.
I'm just trying to create a formula in a cell that will count the cells in a range that don't have a "nil" fill color. Molly F wrote: > I'm just trying to create a formula in a cell that will count the cells in a > range that don't have a "nil" fill color. I think you can only do that with a VB script....... -- gordonATgbpcomputingDOTcoDOTuk To email me replace the obvious! http://www.cpearson.com/excel/colors.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Molly F" <Molly F@discussions.microsoft.com> wrote in me...

Question for Milly Staples Out of Office Assistant
The mailbox is internal. We have a .com and .net structure=20 to our domain. Com for external use and Net for internal=20 use. Both are being handled by an Internal Exchange 2000=20 server. >-----Original Message----- >Is the sender internal or external to your Exchange=20 organization? If >external, this is by design for your own good. Depending=20 on the version of >Exchange, you can change this to allow replies and=20 forwards to the Internet, >but it is a BAD idea. > >--=81 >Milly Staples [MVP - Outlook] > >Post all replies to the group to keep the discussio...

CEdit Question #3
I have created a CEdit object (Mutli-Line & Read only) that is updated with information from a worker thread. When it is updated, I would like it to automatically scroll to the bottom, that way the user get up-to-date information about the current processing. I have tried to do a GetScrollRange, SetScrollPos combo buts its not working quite right. afx_msg LRESULT CMyDialog::OnAppend (WPARAM wParam, LPARAM) { char *string = (char *)wParam; m_strLog += string; // Post it UpdateData(FALSE); INT min, max; m_editLog.GetScrollRange(SB_VERT, &min, &max); m_edi...

Quick Exchange question RE: Inter-server messaging
Am I correct in my thinking that when two exchange servers transfer messages to eachother - i.e. mail comes in on server1 for a mailbox that is stored on server2 - they use SMTP to transfer the messages? If i'm wrong what protocol do they use to do this communication. --------------------------------------------------------------------------- Don't annoy the BOFH... Read all about it here: http://bofh.ntk.net/Bastard.html It uses RPC "George Beech" wrote: > Am I correct in my thinking that when two exchange servers transfer messages to eachother - i.e. mail comes in...

question on view
I notice when I open a certain spreadsheet document it then leaves all these dotted lines all over the place with my spreadsheet. How do I get rid of these lines? Why does this happen? thanks Are you talking about page break preview? It's an option under the View Menu. "Bacchus" <kdoye2112@hotmail.com> wrote in message news:0pKdnSXKJfYHRN_eRVn-hw@rogers.com... >I notice when I open a certain spreadsheet document it then leaves all >these > dotted lines all over the place with my spreadsheet. How do I get rid of > these lines? Why does this happen? >...

Copy the contents of a range of cells to a single comment (indicator)?
How do you copy the contents of a range of cells to a single comment (indicator)? I have a range of cells ("n12:n14"), and want to copy the contents into a single comment indicator within the activecell. anybody help? Thanks Mik On 29 Apr, 23:11, Mik <mhol...@safetysystemsuk.com> wrote: > How do you copy the contents of a range of cells to a single comment > (indicator)? > > I have a range of cells ("n12:n14"), and want to copy the contents > into a single comment indicator within the activecell. > > anybody help? > > ...

SMTP Error Question
Hi, I have users complaining they recieved errors when sending messages to a particular domain, and they have forwarded me the message they have received and I have included below. I understand code 550 probably means something happen remotely but it does not mention what sort of problem (like access denied, user unknown etc.), so I would like to know if anybody can get more information out of it. Thanks for any input in advance, Mark =========================================== Your message did not reach some or all of the intended recipients. Subject: RE: Call from Cecilia ...

Sum question
In one sheet, there are two columns, one is named product item and another is quantity. Since there are many product item number, some of them are same and some of them are different. I want to sum all the item quantity which have the same product item number and show it in a new column. What should I do? Thanks a lot. One way is to use SUMIF Assume you have in Sheet1, cols A and B data from row2 down .. Prod Qty 111 100 112 200 111 150 112 250 etc In Sheet2 ------------- With the product #s listed in A2 down Prod Qty 111 ? 112 ? 113 ? Put in B2: =SUMIF(Sheet1!A:A,A2,Sheet1!B:B) Co...

tangent excel question #2
i appreciate the reply but i dont have autocad installed and dont reall have access to it. i do like the idea of superimposing the c over the but im not sure ill be able to do that in excel. the only way may be t make a tiny picture file of CL. if i knew of a way to make my own fon then that would work as well, but unfortunately i dont -- lsu-i-lik ----------------------------------------------------------------------- lsu-i-like's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1431 View this thread: http://www.excelforum.com/showthread.php?threadid=27102 ...

Question about CListCtrl extended style
I have set the style: m_ListCtrl.SetExtendedStyle( LVS_EX_GRIDLINES | LVS_EX_CHECKBOXES ); What I want to get is: 1. when one item is selected, the check box should be checked; If one item is not selected, it should be unchecked. 2. If item is checked, it should be selected; if not checked, it should be unselected. How to do that? You will have to do this all yourself since that is not the default beheavor of a CListCtrl. The selection and checkboxs are normally independent of each other. But you can simply use a multi-select list control, and as the user checks or unchecks the checkb...

name a range from the current activecell down 12 rows
Hi, I know this is probably very simple but I can't find the code i need and when I try to record as a macro i get hard coded addesses (c7.c19) rather then just something like "xldown 12." and since the starting address varies, the hardcoded address don't help. I want to select the activecell and 12 or 13 down and then give that range a name. Thk in advance for any help. ActiveCell.Resize(13, 1).Name = "myName" MsgBox Range("myName").Name Regards, Peter T "BRC" <brc1051-googrps@yahoo.com> wrote in message news:a2254...

Publisher 2003 Question. This is a HARD one, hope you can help
In Publisher 2002, I can cut content from a page, open another document and paste it and it will paste in EXACLY on this page where I copied from the other. For example, I have a square in the upper right corner of a Publisher 2002 document. I copy it and open another Publisher 2002 document. When I select paste, it puts it in the upper right corner just like the original document. It didn't matter where my cursor was when I pasted. It came right in to that spot. But now that I've upgraded to 2003, when I copy and paste in, it seems to paste wherever my cursor happens to be at ...

Rules Usage question
How ARE we supposed to use rules? For me, I correspond with a lot of clients. Each client has their own folder in Outlook, and pre-exchange I had a rule for each client. Very simple: When an email arrived from a specific address, move it to a specific folder. Can't get much more simple than that. Once we setup SBS2k3, all the sudden I ran out of space on the Exchange Server, since it has a rules limit size of 32k. I could not add rules! What's up with that? Why is there no limit in Outlook, but there is a limit (and a very small one at that!) in Exchange! What a frustration! Now the ...

Smarlist Questions
Hi All Does anyone know of a way to display the notes field in a smartlist? What i need is to have the text typed in the Notes (The OLE note) displayed in a smartlist. I have smartlist builder but I can figure out a way to link the two. Thanks Fliehigh Do you mean the record-level note? If so, link the NOTEINDX field in the appropriate master record to the same field in the SY03900 table and display the TXTFIELD column. An interesting feature of SmartList is that, although the note may only display one line on the screen, if you roll your cursor over it the entire note will pop up i...

sequential date related question
Hi - If for example within cell A3 the date of 09/03/2001 was entered would it be possible in the cell beneath it, A4, to write code tha would break the date down into parts (e.g., Month, Date and Year) an to modify these parts individually? Therefore within cell A4 the code would suggest that it equaled A3 bu that it would have the potentail to modify its parts (e.g., Month, Dat and Year) and not as a whole (e.g., A3+365) Thus what I would like to place in cell A4 is code that would b somehow similar to the following =IF(MONTH(A3)<>12,DATE(YEAR(A3),MONTH(A3)+1,1),DATE(YEAR(A3)+1,1,1))...

Outlook 2003 junk mail question
I'm getting a lot of junk mail, including viruses, with the From field given as: < >. That is, the From field is a single space. Most of this mail claims (falsely, of course) to come from "Microsoft". I tried to set up a rule that would Junk all mail that didn't have a "@" in the From address. But I can't find a way to do that. Using Outlook 2003, how do I automatically send all mail without a proper email address to the Junk folder? Thanks. Charley By the way, I tried to set up a single space as the address of a Blocked Sender. But Outlook told me ...

Question about number of possible cells in Excel.
The book I'm reading says Excel has 255 worksheets, 256 columns and 65,536 rows. It also says Excel has 16.5 million cells. Is that correct, and if so, how is that calculated? Another thing...it says columns are labeled A through Z, AA through AZ, and so on, up to IV. I typed "IV" into the name box, and couldn't visualize the cell reference. Am I reading it correctly as the letter "I" and the letter "V"? Thanks in advance. Try this in an empty worksheet. Edit|Goto (or F5 or ctrl-g) type: IV65536 That's the last cell on the worksheet in xl97-...

LDAP Directory question
When Outlook opens, a login box "LDAP Driectory" opens up before Outlook proceeds. It lists Server:NULL, Port:3268, User: NULL and has no password. How can I bypass this box? Is there a repair that's needed for the LDAP Directory? The LDAP has something to do with my Address Book , which I will need to rebuild after changing from Outlook Express; I installed Office 2003 and could not use OE anymore, and after reloading IE 7 the address book in OE was gone... -- rickt Remove it from your mail profile. (At main Outlook window... Tools > Email Accounts > View/Change...

Question from new user about transfer
I have Microsoft Money Plus Deluxe. Can someone please explain to me How do I handle the following case. My friend needs money so I transfer Amt X from my account to his. Then when he is ready to pay me back he transfers the Amt X to my account. What categories should I use so that these dont show up as expense or Income. Do I need to create a cash account or something? Thks There are many ways. You could create a Loan Account if you expect regular payments. You'd have to fake out the Transfer into the loan, but there are ways. You could use some expense category and then a negativ...

Custom Toolbar Button Question
Hello , I have a GP 9 and I would like to create a custom toolbar button that executes some Dex code. I need to be able to access the data on the current form because I want to invoke an a third party api using some of the field values as variables to pass into the third party api as parameters. I have the dex code for the third party api working fine but I was wondering if someone could point me to a sample. What content objects are available? Thanks, gsvi HI, I have done this before and once you master the concept it's not too hard. It is very close to implementing menu command...

Counting unique items in a range
Hi all I found some useful examples in Chip Pearson's site for counting unique items in a range. These are all based on variations of an array formula of the form {=sum(expression)} I tried without success adapting these to the form {=subtotal(9,expression)} in order to count the unique visible items in an autofiltered range. Help, please? -- Return email address is not as DEEP as it appears Hi think I 'captured' this formula from Peo Sjoblom: Use the following array formula (entered with CTRL+SHIFT+ENTER): =SUM(--IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW (A2:A100)),,1...

Keyboard question
Windows 7 pro x64, Logitech keyboard and mouse wireless bundle. ??????????????????????? Traditional Spanish keyboard layout, sometimes, nearly always, accent, �, ^,etc. 1st hit the accent after the vowel and most of the time I got: ��a, ��e,��i,��o,��u ��a,��e,��i,��o,��u instead of: �, �, �, �, � �, �, �, �, � how could this be solved?? Corsair Corsair, Please help me out understanding your question. Have you set in regional options that your input language is Spanish (Spain) and that your keyboard layout is Spanish, right? So what is it that you are no...

New user question on Pie Charts
Hi, Just got started on Mloney 2006. I pay almost everything by credit catrd and then pay it off at the end of the month to get the 2 percent rebate. However in the pie charts showing all my expenses the expenses take up 50% of the pie chart but are distorted by the fact that my bank transfer to pay the card is treated as an expense so the credit card is 50% also when actually the credit card expenses already show up all correctly categorized. How do I astop this? I used category "bank transfer/cc" to pay the credit card (which ultimately paid all other expenses. -- ATB ...

Getting the 2nd largest or smallest valuesin a range
Hi there Th MAX & MIN functions return the largest & the smallest values in a range. Is there a way to get the 2nd largest and smallest values? Any help appreciated. Thanks Michael One way: =LARGE(rng,2) =SMALL(rng,2) In article <t5slv09qjkald926sr47560g2l89hgu4k7@4ax.com>, Michael Rekas <rekas@worldemail.com> wrote: > Hi there > > Th MAX & MIN functions return the largest & the smallest values in a > range. > > Is there a way to get the 2nd largest and smallest values? > > Any help appreciated. > > Thanks > ...

Workday Function Question
I am using the workday function to build a schedule. This function counts the workweek as Monday thru Friday, but I'm working with a Monday thru Saturday workweek. Is there any way to adjust the function? Or maybe there is anther type of workaround? Thanks for any help. >I am using the workday function to build a schedule. This function > counts the workweek as Monday thru Friday, but I'm working with a > Monday thru Saturday workweek. Is there any way to adjust the > function? Or maybe there is anther type of workaround? From a previous post of mine.... Here is a funct...