copying between cols using autofilter

Hi,
I am relatively inexperienced with Excel 2k autofilter option.  I have
a very large worksheet, with approx 20k number of rows, and 8 cols. I
am interested in 2 cols which are adjacent to each other on the
worksheet.
The first col has data (text only), the 2nd col has a certain no. of
blank fields. It is the blank cells that I am interested in.

I use the auto filter option and using the pull-down list on the 2nd
col select and select only the blank cells. I now wish to copy the
contents of the the first col to the 2nd col, where there are blank
fields, so in effect renaming that cell with the contents of the cell
in the first col.

Currently, I cannot do it as Excel only lets me copy contents of rows
which are directly next to each other.

Any help would be appreciated and thanks in advance.

Gaurab
0
gaurab (4)
10/21/2003 4:32:30 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
354 Views

Similar Articles

[PageSpeed] 4

Why not just do it without the autofilter. If you select a range in col C
then blanks will be filled in from col B.

Sub fillinblanks()
For Each c In Selection
If c = "" Then c.Value = c.Offset(, -1)
Next
End Sub

"Gaurab" <gaurab@ebi.ac.uk> wrote in message
news:3f955f90.719138625@news.hgmp.mrc.ac.uk...
> Hi,
> I am relatively inexperienced with Excel 2k autofilter option.  I have
> a very large worksheet, with approx 20k number of rows, and 8 cols. I
> am interested in 2 cols which are adjacent to each other on the
> worksheet.
> The first col has data (text only), the 2nd col has a certain no. of
> blank fields. It is the blank cells that I am interested in.
>
> I use the auto filter option and using the pull-down list on the 2nd
> col select and select only the blank cells. I now wish to copy the
> contents of the the first col to the 2nd col, where there are blank
> fields, so in effect renaming that cell with the contents of the cell
> in the first col.
>
> Currently, I cannot do it as Excel only lets me copy contents of rows
> which are directly next to each other.
>
> Any help would be appreciated and thanks in advance.
>
> Gaurab


0
Don
10/21/2003 5:16:31 PM
Hi Don, 
Is the snippet of code  to run a macro? Where do I actually run the
code? Sorry, to a be a total greehorn about this.

Thanks,



On Tue, 21 Oct 2003 12:16:31 -0500, "Don Guillett" <donaldb@281.com>
wrote:

>Why not just do it without the autofilter. If you select a range in col C
>then blanks will be filled in from col B.
>
>Sub fillinblanks()
>For Each c In Selection
>If c = "" Then c.Value = c.Offset(, -1)
>Next
>End Sub
>
>"Gaurab" <gaurab@ebi.ac.uk> wrote in message
>news:3f955f90.719138625@news.hgmp.mrc.ac.uk...
>> Hi,
>> I am relatively inexperienced with Excel 2k autofilter option.  I have
>> a very large worksheet, with approx 20k number of rows, and 8 cols. I
>> am interested in 2 cols which are adjacent to each other on the
>> worksheet.
>> The first col has data (text only), the 2nd col has a certain no. of
>> blank fields. It is the blank cells that I am interested in.
>>
>> I use the auto filter option and using the pull-down list on the 2nd
>> col select and select only the blank cells. I now wish to copy the
>> contents of the the first col to the 2nd col, where there are blank
>> fields, so in effect renaming that cell with the contents of the cell
>> in the first col.
>>
>> Currently, I cannot do it as Excel only lets me copy contents of rows
>> which are directly next to each other.
>>
>> Any help would be appreciated and thanks in advance.
>>
>> Gaurab
>

0
gaurab (4)
10/21/2003 11:20:42 PM
Another way without the filter:

Select that 2nd column.
Edit|goto|special, click on Blanks
in the active cell: type = and point to the cell on the same row that you want
copied.

hit ctrl-enter instead of just enter and the formerly blank cells will be filled
with formulas that point at the data cell.

Then you can convert them to values (select the column|copy|paste special
values)

Debra Dalgleish has nice pictures at:
http://www.contextures.com/xlDataEntry02.html

But in her example, she's filling cells vertically.

=======
Or when your data is filtered to show just the blank cells, you can type the
formula for one cell and drag down through the range.  Only the visible cells
(formerly blank) will be filled with the formula.

And then convert to values (if you want).

Gaurab wrote:
> 
> Hi,
> I am relatively inexperienced with Excel 2k autofilter option.  I have
> a very large worksheet, with approx 20k number of rows, and 8 cols. I
> am interested in 2 cols which are adjacent to each other on the
> worksheet.
> The first col has data (text only), the 2nd col has a certain no. of
> blank fields. It is the blank cells that I am interested in.
> 
> I use the auto filter option and using the pull-down list on the 2nd
> col select and select only the blank cells. I now wish to copy the
> contents of the the first col to the 2nd col, where there are blank
> fields, so in effect renaming that cell with the contents of the cell
> in the first col.
> 
> Currently, I cannot do it as Excel only lets me copy contents of rows
> which are directly next to each other.
> 
> Any help would be appreciated and thanks in advance.
> 
> Gaurab

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/22/2003 2:54:33 AM
Thanks Dave,
That did the trick, and thanks to others who replied.


On Tue, 21 Oct 2003 21:54:33 -0500, Dave Peterson <ec35720@msn.com>
wrote:

>Another way without the filter:
>
>Select that 2nd column.
>Edit|goto|special, click on Blanks
>in the active cell: type = and point to the cell on the same row that you want
>copied.
>
>hit ctrl-enter instead of just enter and the formerly blank cells will be filled
>with formulas that point at the data cell.
>
>Then you can convert them to values (select the column|copy|paste special
>values)
>
>Debra Dalgleish has nice pictures at:
>http://www.contextures.com/xlDataEntry02.html
>
>But in her example, she's filling cells vertically.
>
>=======
>Or when your data is filtered to show just the blank cells, you can type the
>formula for one cell and drag down through the range.  Only the visible cells
>(formerly blank) will be filled with the formula.
>
>And then convert to values (if you want).
>
>Gaurab wrote:
>> 
>> Hi,
>> I am relatively inexperienced with Excel 2k autofilter option.  I have
>> a very large worksheet, with approx 20k number of rows, and 8 cols. I
>> am interested in 2 cols which are adjacent to each other on the
>> worksheet.
>> The first col has data (text only), the 2nd col has a certain no. of
>> blank fields. It is the blank cells that I am interested in.
>> 
>> I use the auto filter option and using the pull-down list on the 2nd
>> col select and select only the blank cells. I now wish to copy the
>> contents of the the first col to the 2nd col, where there are blank
>> fields, so in effect renaming that cell with the contents of the cell
>> in the first col.
>> 
>> Currently, I cannot do it as Excel only lets me copy contents of rows
>> which are directly next to each other.
>> 
>> Any help would be appreciated and thanks in advance.
>> 
>> Gaurab
>
>-- 
>
>Dave Peterson
>ec35720@msn.com

Gaurab
0
gaurab (4)
10/22/2003 9:40:35 AM
Reply:

Similar Artilces:

File/"copy to Folder" access
I am not able to find the "Copy to Folder" option, under the File dropdown, when replying (or forwarding) a message. I have tried adding it from the "customize" function, but was unsuccessful. Any ideas? What version of Outlook are you using? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "news.hp.com"...

Line used to separate text sections in email
I use Outlook 2003 and received an email from someone using same system. The email I received was written in several sections and to separate each section was a black bold line spanning the email, as if underlining the text above. The lines are neat and make the email very clear; you can't click on them to get the little 'handles' though, so they weren't manually drawn / autoshapes. They're all the same length and extend only as far as the text does on either side of the email, and it's difficult to do that manually... If you resize the window, the l...

Copy pages from previous publications
I'm creating a 32 page document for a marching band program. Some of the pages, including the cover, will be re-used from last year's publication. I created this on Publisher 2003 and am using the same this year. I can't figure out how to move the needed pages from last year's publication to the current project. I'm not giving up my day job! Thanks in advance for any help. Band Mom wrote: > I'm creating a 32 page document for a marching band program. Some of > the pages, including the cover, will be re-used from last year's > publication. I created thi...

Inability to use links
When I click on a link in my email, nothing happens. If I post the same link in Explorer, it works fine... the web site appears as advertised. I assume that the assocaition between IE and Outlook is broken but I don't know how to fix. Some hints on how to fix would be appreciated. Bruce Are you looking in the preview screen? If you actually open the message it should then become active. You might also have to hold ctrl when you click it. >-----Original Message----- >When I click on a link in my email, nothing happens. If I >post the same link in Explorer, it works ...

sorting using macro
I created a series of macros that sort my spreadsheet by a different column heads as needed, and assigned them to buttons, so I can sort by those column when I want to by just clicking. It works fine, until I go and do something else in the sheet, like highlight a range of data for printing. After that, the macro fails and when I click debug, Below is one of the macros that sorts the column headed by names (names is in A1)Any ideas what I can do to fix this? Thanks in advance. Ross Sub sortnames() ' ' sortnames Macro ' Macro recorded 9/14/2005 by ross D ' ' S...

Conditional formatting using OR() & AND()
I am trying to find a formula to use for condition formatting. I trie using this, but I know my formula is messed up/ not formatted right. used: =OR(N4<>0,P4<>0) AND(X4="Y",R4<>0) I want the formula to say: Apply conditional formatting if: X4=Y and R4<>0 AND either N4 OR P4 is <>0 (but not both--it's not possible for both cell to have an entry.) So, in total I am looking for 3 cells in order to apply conditiona formatting. Can anyone help? Thank you ----------------------------------------------- ~~ Message posted from http://www.ExcelT...

Challenge using SQL
To first explain, we have a server that contains 5.5 million records of financial accounts (I work for a debt collection agency). Some people may have records with multiple debts with different banks (or whatever). Some of these duplicates will contain phone numbers and what I need to do is populate records with no phone numbers from the matched duplicates. I have imported 40,000 records into a stand-alone access database to do some testing. I have created a duplicates query and create a new column that classifies valid and non valid phone numbers. But where do I go from there ... any...

Using Target.Address in Excel 98
Hi I have a code which as Target.Address which works in Excel 2000 but will not work in excel 98. Does anyone know what code to use for when a workbook change is to take affect. The current code which works in Excel 2000 is below: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$G$26" Then If Range("G26") = "Other" Then ActiveSheet.Unprotect Sheets("Recording Sheet").Range("E28") = "Please speficy:" Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 2 ...

Add number of used rows to footer?
Is it possible to add the number of used rows to a spreadsheet's footer? I have a basic list in Excel 2000. the number of rows in the list changes weekly, and I would like to find a simple way to: 1 - Count the number of rows in the current list (minus the header row) 2 - Display this number in the footer of the printed spreadsheet. If anyone can help with either of these, I'd appreciate it! Thanks, E If there are no embedded blank cells in column A, you can get a count of the filled rows (minus the header) with the formula =COUNTA(A:A) - 1 To put that number in a footer, you nee...

VBA copy cell to another worksheet
Hello, I am trying to copy text information from one worksheet to another. I open only the first sheet then I have a comand button by where I want to say transfer the values. La macro give an error at ObjWorshett.Cells(Riga, 1).text = oDO.GetText Errore di run-time '1004'. Errore definito dall'applicazione o dall'oggetto. Thanks in advance! Franco Dim oDO As New DataObject oDO.SetText [B2].Value oDO.PutInClipboard Dim ObjWorshett As Worksheet Dim strNomeFile As String strNomeFile = "\\srv01\Dp\ANTONELLA\sblocco.xls" Set ObjWorshett = Appli...

Line charts using temperature and time factors
I want to create a line chart showing comparisons of heating and time. For example one item is held @ 30deg for 10secs then 100degs for 30secs while the comparison is plotted to show the difference. Help appreciated. try x-y scatter chart data-columnwise. Excelon_Ls <Excelon_Ls@discussions.microsoft.com> wrote in message news:6B9F5547-A35C-4272-9FDC-F2CE6122D783@microsoft.com... > I want to create a line chart showing comparisons of heating and time. For > example one item is held @ 30deg for 10secs then 100degs for 30secs while the > comparison is plotted to show the diff...

How can I generate a report by using the check box?
I am trying to generate a report to open for only the boxes I have check marked, how can I do that? Does this question make sense? No, your question doesn't make much sense. Do you have a table structure, sample records, and desired output? -- Duane Hookom Microsoft Access MVP "Kat" wrote: > I am trying to generate a report to open for only the boxes I have check > marked, how can I do that? Does this question make sense? ...

How can i copy a value in a cell from a sheet and paste in anothe.
Hello, I ask my question with an exemple : I want to copy a value in C2 from sheet1 to D5 on sheet2. But , if i modify C2 on sheet1, D5 sheet2 has to be modified too. Do you know the function to do that? Thanks :) ( Sorry for my bad english, i speak french usually ) Try putting this in D5 on sheet2: =Sheet2!C2 Maybe this is what you want. Art Hi use the formula ='sheet1'!C2 -- Regards Frank Kabel Frankfurt, Germany "clapton" <clapton@discussions.microsoft.com> schrieb im Newsbeitrag news:F401E623-B2F0-463C-8E0E-04874B24F195@microsoft.com... &...

Using Tab Control in Dialogs?
Hello, I want to use tab control in my dialog. I can add page to my tab control, but how I can edit tab contents? (I don't want to use property sheets.) I'm going to use dialog resource as tab page contents. Thanks in advance. Sadjad Fouladi wrote: > Hello, > I want to use tab control in my dialog. I can add page to my tab > control, but how I can edit tab contents? (I don't want to use property > sheets.) I'm going to use dialog resource as tab page contents. > > Thanks in advance. For each page, create a modeless dialog template and class. Turn off t...

Publisher 2003-How does one learn how to use it?
I'm disgusted with Microsoft. I purchased Microsoft 2003 with digital imaging expecting to find manuals on how to use the product. There was a manual for the digital imaging, but none for Publisher. How does one learn how to use Publisher, through osmosis? Not including an operating manual is like going into a restaurant and ordering your meal only to find out the restaurant doesn't supply silverware. Does anyone know ehere I can get a manual or training? I appreciate your responses. Ron strange you think Microsoft would care about what you think. Ron Publisher up to version...

Clip Art Searches
Why can't I use more then one word to search in clip art? For example, I want to find "Christmas Border" but I can only search for "Christmas" or seperately for "Border" both of which turn up 100+ pages. There are Christmas borders out there but I am forced to go through 100's of pages to find them. Select Special Occasions in the categories, input Christmas or borders, there aren't that many Christmas borders. There is an options link on the clipart page on the right top, click it and select 48, this will help. -- Mary Sauer MSFT MVP http:/...

Using Different "Reply Address" in Outlook
I'm not sure which Newsgroup would be more appropriate so I try both Exchange and Outlook. Our organization has two email domains, one hosted by an ISP and the other maintained in-house. In the office, we already have an Exchange server hosting mail for Domain-A. All the workstations are running Outlook 2000 to connect to their mailboxes. The ISP is hosting email of Domain-B for us. Users are using web access to connect to the ISP to get messages for Domain-B. I am trying to standardize Outlook 2000 as the only email client by adding the mail profile of Domain-B. To do this, I change the...

Using Delivery options to forward email to outside address
NT 4.0 Domain. Exchange 5.5. How can I configure an account to forward email to an outside email address such as a users home address. Thanks in advance James Set up a custom recipient for the external address. Use alternate delivery on the mailbox properties to deliver to the custom recipient. Then hide the custom recipient from the GAL if you wish. James Raaymakers wrote: > NT 4.0 Domain. Exchange 5.5. How can I configure an > account to forward email to an outside email address such > as a users home address. > > Thanks in advance > James Thank you very much. >...

Not able to get private appointments programaticaly using Outlook 2002
We currently have Outlook 2002 and shared a resource calendar to his/her manager. The manager can see the time blocked for the resource's private appointments. But when we get the appointments programaticaly using MAPIFolder and DAppointmentItem objects using a C++ programm we are not able to get the private appointments. We used to get the appointments using Outlook 98 and by checking the Sensitivty value we were able to identify it as a private appointment Thanks, Aresh ...

We can't use a public folder, we can see it?
I have created a Group mailing folder in 'All public folders', set the permissions for my colleagues. they can see the folder on the navigation page, but cannot see it in the list when they create a new email, why? We are using 2007. Is this a Contacts folder? You'll have to add it to your Public Folder Favorites first. See http://www.msoutlook.info/question/292 After that, you can configure the properties of the Contacts folder to show as an Outlook Address Book. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto...

Within CRM, use Outlook, instead of the standard e-mail-activity
From within CRM, is it possible to open Outlook on ALL new e-mail activities in CRM (instead of using the default web-based form) ? The default web-based form is great, however I'd perfer to use Outlook itself, for all my mails. I know, I can start a new mail from outlook and assisgn it to a CRM-object from within the Outlook-client. However, since we have a lot of contacts and accounts, we often find a contact, by selecting the account first, or else by usage of the Advanced Search feature. Therefore, I'd like to start this mail from within CRM itself, but then use Outlook, in...

I'm I the only one pissed off at BofA for changing the format they use for reporting transactions?
Instead of putting the location where the transaction took place, the transaction entry now starts with: BANK CARD PURCHASE 0980980923482348923 WTF is that? Now I'm forced to manually change the payee so that the transactions auto-classify. Am I the only one noticing this? If not, please send an email to them about it through their site. Maybe if enough people complain they'll fix this problem. Its a long shot, but worth a try. njohn said the following: > Instead of putting the location where the transaction took place, the > transaction entry now starts with: BANK CARD PURCHA...

Copy and Paste Options
I have never had this happen before but today my computer in Excel will not allow me to right click and use copy, paste, or cut. They are grayed out and I have tried all settings to correct. The sheet is not protected and I have tried new docs also. ...

following hyperlinks using the enter key no longer works in excel xp/2k3...
in excel 2000 you can navigate an excel spreadsheet with the arrows keys and use the enter key on a cell with a hyperlink and it goes to the destination of the link. in excel xp and 2k3 that no longer works. hitting the enter key just jumps to the next cell. i turned off that feature, but then hitting enter does nothing at all. is there a way to get the functionality back that was in excel 2000? thanks I've seen a few posts that ask this same question and I've never seen a way to turn it back to the way it used to be. ryan wrote: > > in excel 2000 you can navigate ...

Copying formulas #12
Hi all, I have a row of forumulas, which relate to columns of data. Each formula needs to be copied down the sheet, but I need the column value to change, not the row value. eg C3 : =sum(c2:c31) C4 : =sum(d2:d31) C5: =sum(e2:e31) etc. How can I replicate the formulas down, but get the Column values to change ? TIA Stu Ignoring the issue of Circular references (the formula in C3 references a range that includes C3)... I *think* this will work for you Put the formula =SUM(C$2:C$31) into an empty row in your spreadsheet and copy it across to all the columns you want to su...