How can I skip blank cells when copying columns?

How can I copy an entire column of data(values) without copying the
blank cells too, so that when I paste my column into my other
worksheet, I won't have to sit there and delete the blank cells. Thanks
in advance. :p


---
Message posted from http://www.ExcelForum.com/

0
1/4/2004 6:42:18 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
380 Views

Similar Articles

[PageSpeed] 54

Try Advanced Filter  with comuted criteria...

Let A1:A100 house the data of interest. A1 contains a distinctly formatted
label.

Leave D1 empty.

In D2 enter:

=A2<>""

Select A1:A100.
Activate Data|Filter|Advanced Filter.
Check the box for "Copy to another location".
Enter, if needed, $A$1:$A$100 in the box for List Range.
Enter $D$1:$D$2 in the box for Criteria range.
Enter $E$1 in the box for Copy to.
Leave the box for "Unique records only" unchecked.
Click OK.

"Mary19 >" <<Mary19.zhvog@excelforum-nospam.com> wrote in message
news:Mary19.zhvog@excelforum-nospam.com...
> How can I copy an entire column of data(values) without copying the
> blank cells too, so that when I paste my column into my other
> worksheet, I won't have to sit there and delete the blank cells. Thanks
> in advance. :p
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
akyurek (248)
1/4/2004 10:02:59 AM
You could even just use Data|Autofilter.

Copy your list and paste it.
Apply Data|filter|autofilter
use the dropdown to show just the blanks
delete those visible rows.
Remove the filter.

Or do the filter on the original data
Data|filter|autofilter
but filter on non-blanks
select the range
edit|goto|special|visible cells only
paste to the new location

======
and one more

copy the range
paste it to the new location
select the new range (whole column)
edit|goto|special|blanks
Edit|delete|Shift cells up 
(or entirerow--if it's just a single column, there'd be no difference)


"Mary19 <" wrote:
> 
> How can I copy an entire column of data(values) without copying the
> blank cells too, so that when I paste my column into my other
> worksheet, I won't have to sit there and delete the blank cells. Thanks
> in advance. :p
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/4/2004 3:44:40 PM
Reply:

Similar Artilces:

Can you make excel auto save your workbook?
I need Excel to automaticly save my work every 2 or 3 minutes if that is possible. Can it do that? Hi what Excel version are you using? -- Regards Frank Kabel Frankfurt, Germany "Rhiannon" <Rhiannon@discussions.microsoft.com> schrieb im Newsbeitrag news:FE6930C8-D9D4-46D0-825E-0C25A02C1B71@microsoft.com... > I need Excel to automaticly save my work every 2 or 3 minutes if that is > possible. Can it do that? Excel 2003 "Frank Kabel" wrote: > Hi > what Excel version are you using? > > -- > Regards > Frank Kabel > Frankfurt, Germany &g...

Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my names from McLaughlin, Victor, (i.e, comma) W to McLaughlin, Victor.(i.e., period) W Is there an extract and replace formula or method of som sort (in excel or access) that will allow me to pull the first comma from the right and replace it with a period. Thanks for any suggestions! Select the cells you want to change and run this tiny macro: Sub comma_tose() For Each r In Selection v = StrReverse(r.Value) r.Value = StrReverse(Replace(v, ",", ".", 1, 1)) Next End Sub For example: a,b,c,d wi...

How can I print to email?
I want to send the results from some sheets to people who do not have Excel. If I attach the XLS file, it will be useless to anyone without Excel. I basically want to send what I see on a printout to a printer or Fax. Any ideas? Is there a way to setup email as a printer? This will enable me to use the same method for Word, Powerpoint and other products without caring about the recepients software. Jim Hi Jim You can send the text in the body of the mail I don't know what Excel version you use and which mail program? Or let them download the free Excel viewer You can view and ...

how can I show leading zeros in excel
I am trying to input some numeric data into a spreadsheet, some of the entries have leading zeros, every time I move to the next cell, Excel changes the data by removing the leading zeros. I have tried reformating the cells in question in Number & General, both give me the same problem. Can somebody please help. Using Excel 2002. Thanks. You could either format the cell as Text (then type your data) or you can start with an apostrophe '000123 or you can use a custom format: Format|cells|number tab custom in the type box, put as many 0's as you want 000000 The first two op...

Money Can't Set up a Mortgage Right!
I have issues with Money 2003. I bought a house Nov 2003. I set up the mortgage account and tell it my purchase date. It starts the loan 1/1/2004 because it assumes I have paid Nov interest at closing and won't owe anything until 1/1/2004. This screws up my net worth reports because I had a home asset in Nov 03, but the loan liability doesn't appear until Jan 04. Broken! I can't seem to set the loan date to force it to recognize the Nov loan beginning date. What to do?? Thanks, Ben Money starts the loan in 1/1/2004 because that's the date you told it the first payment...

How do i copy conditional formating formulas from 1 row to rest
I have got conditional formatiting set up to highlight an entire row when a field says OVERDUE ie. Formula is =$N$4="OVERDUE" When N4 is overdue How do i set this conditional formating for every row in the workbook without writing each one manualy Change $N$4 to $N4. Then you can copy the cell and paste special formats to the entire workbook. "Rizlaburn" wrote: > I have got conditional formatiting set up to highlight an entire row when a > field says OVERDUE > ie. Formula is =$N$4="OVERDUE" When N4 is overdue > > How do i set this condit...

Combine multiple rows into one row with multiple columns
Hi, I have a table set up so that there are three columns: StudyID, DrawDate, and Value. StudyID and DrawDate are the primary key. I want to create a table from this one that has only one row for each StudyID so that it would go from: StudyID DrawDate Value to StudyID DrawDate1 Value1 DrawDate2 Value2 DrawDate3 Value3 etc. Is there a way to do this? Thanks, Elysia "Elysia Larson" <elysia.larson@gmail.com> wrote in message news:832e952f-174f-489f-ab7a-e2189f660a92@f6g2000vbp.googlegroups.com... > Hi, > > I have a...

How can I initialize the value?
template < class elemType > class MyArray { public: explicit MyArray( int size = DefaultArraySize ); MyArray( elemType *array, int array_size ); MyArray( const MyArray &rhs ); virtual ~MyArray() { delete [] ia; } bool operator==( const MyArray& ) const; bool operator!=( const MyArray& ) const; MyArray& operator=( const MyArray& ); int size() const { return _size; } virtual elemType& operator[](int index) { return ia[index]; } virtual void sort(); virtual elemType min() cons...

Can Function results be calculated?
Hi all I have a sheet with two columns of figures. I show the running totals of each column in cells with basic SUM Functions, at the top of the sheet. I now want to subtract one total from the other. How is this done? As the Function is looking at cells that themselves hold Functions, I can't see how to do this, short of making a longer Function that adds the column contents again and then subtracts. So, in summary: I have two cells with Functions. These are: Cell F4 =SUM(F8:F45) and Cell G4 =SUM(G8:G45) I want to do this: =F4-G4 but it won't play. Any advice most appreciated....

Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in this particular sheet it should be cell DA197). I have used both the methods described in the Knowledge Base article (deleting rows and columns and re-saving; and the Excess Format Cleaner add-in). Deleting the rows and columns does not work; using the Excess Format Cleaner does not work either and it then also hides the rows from 198 to 65536 - but does not do the same for the columns. I have checked that there is no protection on the worksheet. Has anyone else come across this problem and if so can you please ...

Column to Rows
I want to convert my data from one column into rows. I have my data set up now as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 I want it to be displayed into 4 separate columns as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 Please advise, thanks, Don -- Don D. ------------------------------------------------------------------------ Don D.'s Profile: http://www...

Right column spilling onto 2nd page
I bought a template from a vendor and saved it as a new template with my version of excel. The link to that file is: http://www.utahhousevalues.com/lgfiles.cfm. The vendor is not able to help me figure out why the right column is spilling onto a second page. There are 49 total pages and about 25 of them spill the right column onto a second page. They claim each page is identical and it should not do that. I'm using Small Business version of Excel 2000 with XP Professional SP-1 THANKS MUCH Darrell Catmull http://www.utahhousevalues.com Hi Darrell, If the vendor can't tell you w...

How can I list active OWA users
We have two front end Exchange servers running Exchange 2003. I need to list the active OWA connections on each server. What is the best way to do this? Thanks in advance MikeC MikeC <MikeC@discussions.microsoft.com> wrote: >We have two front end Exchange servers running Exchange 2003. I need to list >the active OWA connections on each server. What is the best way to do this? Use WMI? -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm Don't send mail to this address mailto:h.pott@getronics.com Or to these, either: ma...

Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not intended), even if I have specified 'Series in Rows'. This happens intermittently, and I am not sure what I am doing wrong. I do save the workbook as Microsoft Excel 97 so that a user with Excel 2000 or Excel 2003 can use the workbook. Thank you for any suggestions. Hard to tell if you keep the code secret. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peac...

outlook 2003 send sometimes skips recipients
I am supporting IT at my company. We have seen a problem with employees running outlook 2003 with exchange 2003. When employees send email to a distribution group with say numerous members bet 3 and 20 recipients all on our email server sometimes the email does not go through to all recipients. This happens when sending/forwarding email or new calendar invites. All our Outlook clients and exchange server are running the latest service packs. Has anyone seen this problem? ...

tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

copy sheet1 from bookA to bookB
If I copy a sheet with formulas from bookA to bookb, all formulas than point from bookB to bookA How could I avoid that Same for copy sheet1 to sheet2 Thanks Manfred Once you have copied the sheet across and the formulas are referring to bookA... Eg. ='[BookA]sheet1'!A1+'[BookA]sheet1'!A2 Highlight the entire new sheet, and do a replace (Ctrl H) Find What : [BookA] Replace With : Leave Replace With blank and hit replace all. Obviously you need to have a sheet called sheet1 in your new workbook. Check you have broken all links by going Edit / Links... links should be gre...

Can I protect Livemail on laptop with a password ?
I "download" all my email from Yahoo server and work with it under Livemail on laptop. Laptop is going for a warranty repair - I will have to give the repairer my windows user password which means they will have access to my livemail messages, contacts ec - can I further protect my email in any way while laptop is out of my hands ? "Croz" <Croz@discussions.microsoft.com> wrote in message news:DCD9FA83-09D0-430C-AE26-4AA406D488CD@microsoft.com... > I "download" all my email from Yahoo server and work with it under Livemail on laptop. > Lapt...

Extra Blank Lines
The following macro imports a text file and sorts horse races in time order and puts a blank line in between each race time (which is at the end of the code), which it does admirably. I had some valuable help with this project from group members recently. However, it does create quite a few blank lines en bloc. It's not really a problem, as I search them out and do a delete. Below is the code which a member might use in order to find the cause of the blank lines? Sub Macro1() Range("A2:J601").Select Selection.ClearContents Range("A2").Select ...

Calendar in Cell Validation
I want to implement a cell validation such that when the user attempts to input a date, a "list" box-like functionality pops up that has a calendar and the user may then choose the date by picking with the mouse How would I implement this? Thanks Jerry Try the following Web site. http://www.fontstuff.com/vba/vbatut07.htm This site's author covers this in a tutorial, but also provides downloads. Mark <jerry.ranch@pioneer.com> wrote in message news:2r9t51pjmumjk7rjpopo7fuamg81gqkljq@4ax.com... >I want to implement a cell validation such that when the user attempts &g...

Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and some bank cells and I want it add them and put the total into the second last row (7 in this case). The last row contains a multiplier (3) which when applied to the total results in 21. Below is the table. | | | 3 | | | | | | | | 4 | | | | 7 | | | 3 |21| How can I achieve this in VBA (under Word 2003 and 2007) remembering that the user can add rows to the table and the last column can contain blank cells. Thanks in advance for any assistance, Peter Evans Sub ScratchMaco(...

extract info from cell, then count
I have a 2-part question: (i) I have 1000's of e-mail addresses but want to extract the countr from the e-mail i.e. abc@def.de, where de (Germany) is needed. How d I isolate the ".de" (and others eg .fr, .edu, .com etc etc) (ii) Having done the above, I then need to do a count. Rather than us COUNTIF and include the code for every country in the world, is ther any other way of counting? I guess a Pivot table? thanks, cathal..... -- Message posted from http://www.ExcelForum.com One way: =MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LE...

Skip Records in a Form
I have a form in my database based on a table. The table is appended with new reocrds everyday. My goal is to have the form present only the new records and allow a user to key data into the new record and be recorded in the same table. The issue I have is that the form is displaying all records not just the new. Does anyone know a way to open a form and display only those records that have not been updated by the user? Thanks in advance Add a column for UpdateDate and update this column whenever a record is updated via the form. Set up a query to read all the rows/columns ...

Conditional Formatting
Is it possible to format a portion of a text string within a cell (as opposed to the entire cell). For example, I would like to format the word 'gift' in red font anywhere it a appears in range C2:C417 but only that word, not the entire cell. Not with conditional formatting. But you could change the actual format for that word (or group of characters)... Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating ...