Can I remove blanks from a range without using sort?

I have a range of cells A1:a10, say, which obtain data from another source.  
Some of those cells  a3, a6:a8, say, under certain conditions, will be blank. 
 How do I reorder this range such that the cells containing information are 
listed together, removing the blanks?  I want to do this using a formula, 
rather than filter or sort, as the data, and hence the blank cells, will 
change, and I want to perform analysis on the cells containing data.
0
3/4/2005 11:43:03 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
427 Views

Similar Articles

[PageSpeed] 11

this is

 from one of the newsgroup correspondents

use this code statement

Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete



=================================

Hugh Murfitt <firstdotlast@ntlworld.delete.com> wrote in message
news:0C6E9E21-951E-46E1-A6D6-F03943D1CE03@microsoft.com...
> I have a range of cells A1:a10, say, which obtain data from another
source.
> Some of those cells  a3, a6:a8, say, under certain conditions, will be
blank.
>  How do I reorder this range such that the cells containing information
are
> listed together, removing the blanks?  I want to do this using a formula,
> rather than filter or sort, as the data, and hence the blank cells, will
> change, and I want to perform analysis on the cells containing data.


0
R
3/4/2005 12:53:15 PM
a macro or
make your selection>f5>special>blanks

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Hugh Murfitt" <firstdotlast@ntlworld.delete.com> wrote in message
news:0C6E9E21-951E-46E1-A6D6-F03943D1CE03@microsoft.com...
> I have a range of cells A1:a10, say, which obtain data from another
source.
> Some of those cells  a3, a6:a8, say, under certain conditions, will be
blank.
>  How do I reorder this range such that the cells containing information
are
> listed together, removing the blanks?  I want to do this using a formula,
> rather than filter or sort, as the data, and hence the blank cells, will
> change, and I want to perform analysis on the cells containing data.


0
Don
3/4/2005 1:08:16 PM
I was hoping for a formula that would re-order automatically without losing 
data

"R.VENKATARAMAN" wrote:

> this is
> 
>  from one of the newsgroup correspondents
> 
> use this code statement
> 
> Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> 
> 
> 
> =================================
> 
> Hugh Murfitt <firstdotlast@ntlworld.delete.com> wrote in message
> news:0C6E9E21-951E-46E1-A6D6-F03943D1CE03@microsoft.com...
> > I have a range of cells A1:a10, say, which obtain data from another
> source.
> > Some of those cells  a3, a6:a8, say, under certain conditions, will be
> blank.
> >  How do I reorder this range such that the cells containing information
> are
> > listed together, removing the blanks?  I want to do this using a formula,
> > rather than filter or sort, as the data, and hence the blank cells, will
> > change, and I want to perform analysis on the cells containing data.
> 
> 
> 
0
3/4/2005 1:45:02 PM
Hugh

What type of analysing are you doing on the data?

Most Functions will ignore blanks in a range.

=SUM(A1:A10) ignores the blanks.

To delete the blanks if desired..........

Select A1:A10 then F5>Special>Blanks>OK

Edit>Delete>Shift Up.


Gord Dibben Excel MVP



On Fri, 4 Mar 2005 03:43:03 -0800, Hugh Murfitt
<firstdotlast@ntlworld.delete.com> wrote:

>I have a range of cells A1:a10, say, which obtain data from another source.  
>Some of those cells  a3, a6:a8, say, under certain conditions, will be blank. 
> How do I reorder this range such that the cells containing information are 
>listed together, removing the blanks?  I want to do this using a formula, 
>rather than filter or sort, as the data, and hence the blank cells, will 
>change, and I want to perform analysis on the cells containing data.

0
Gord
3/4/2005 5:45:24 PM
{=INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>0,ROW(A$1:A$10),""),ROW(A1:A1)))} 
(control-shift-enter)  will work if they are really blanks.  If the 
"blank" values are "" returned from elsewhere, they will remain "".  If 
0 is returned, they will be removed.
Note that your 1st reference must be to A1.  Won't work if you use A2, 
for example.

HTH



Hugh Murfitt wrote:
> I have a range of cells A1:a10, say, which obtain data from another source.  
> Some of those cells  a3, a6:a8, say, under certain conditions, will be blank. 
>  How do I reorder this range such that the cells containing information are 
> listed together, removing the blanks?  I want to do this using a formula, 
> rather than filter or sort, as the data, and hence the blank cells, will 
> change, and I want to perform analysis on the cells containing data.
0
hughaskew (1)
3/5/2005 4:01:24 AM
This is EXACTLY what I wanted.  Thanks a million for saving me a huge amount 
of time.

"Hugh" wrote:

> {=INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>0,ROW(A$1:A$10),""),ROW(A1:A1)))} 
> (control-shift-enter)  will work if they are really blanks.  If the 
> "blank" values are "" returned from elsewhere, they will remain "".  If 
> 0 is returned, they will be removed.
> Note that your 1st reference must be to A1.  Won't work if you use A2, 
> for example.
> 
> HTH
> 
> 
> 
> Hugh Murfitt wrote:
> > I have a range of cells A1:a10, say, which obtain data from another source.  
> > Some of those cells  a3, a6:a8, say, under certain conditions, will be blank. 
> >  How do I reorder this range such that the cells containing information are 
> > listed together, removing the blanks?  I want to do this using a formula, 
> > rather than filter or sort, as the data, and hence the blank cells, will 
> > change, and I want to perform analysis on the cells containing data.
> 
0
3/8/2005 8:37:02 AM
Reply:

Similar Artilces:

using data from one worksheet in another worksheet
I have 2 wksheets that have a cell that has a total sum of expenses for that year at the bottom. I then have another worksheet that I'm trying to take the totals (which are using the sum formula for that worksheet) of those worksheets and places it on the this sheet. I have tried the formula of using the = then clicking on the cell of the other wksheets but I get an amt of $0.00 which isn't true. I think it's because the totals on the other wksheets are using the sum formula. Could use any help with this. Much appreciated. Hi J. Please refer to:- http://ww...

Help: Want to use French characters in name and email address
I have a client with an English installation of Windows and Exchange. They have a French employee who wants to be able to use the accents in their name in their email address. 1. To institute this is it just a matter of renaming the user and/or adding another email address using the accented characters or is there more to it (and if so what)? 2. Would there be any problems with OWA as a result of this? 3. Any other issues I should be aware of? Thanks. Kevin From my experience in practice these characters tend to give problems. I worked on many software products and it does happen som...

Can Text in an InputBox appear as asterisks?
Hi All, When an input box appears on the screen I want the text the user types in to appear as asterisks. Is this possible? Basically the effect I want to create is 2 types of user for my workbook. One that can edit all of the workbook and another that can only edit one worksheet. The InputBox will have a message title saying " Type in the password to edit this workbook" hence I do not want others to see what they type in! The worksheets are already protected with a password which I want only me to know so I cant use this! Please help, need a solution by the weekend!! ...

My Ctrl+D doesn't work, how can I reset?
In excel my fill down shortcut does not work. Ctrl+D. Where are these shortcut viewed and possibly reset? There are only two ways I can think of that can prevent Ctrl-d from acting to do a fill down. One is if an external program intercepts that keystroke before it gets to Excel. The other is if, within Excel, that keystroke has been assigned to a macro. One way to check the latter possibility quickly is to start Excel in safe mode. From Start->Run enter "excel.exe /s" (without quotes, there is s pace before the slash). If Ctrl-d works then you know it is a macro lo...

How to print worksheets of the same name in different workbooks without opening
How to print worksheets of the same name in different workbooks without opening each one? -- Message posted via http://www.officekb.com open with a loop>print>close -- Don Guillett SalesAid Software donaldb@281.com "leon m via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:c8b68ead308a4c6487ba89e51726eece@OfficeKB.com... > How to print worksheets of the same name in different workbooks without > opening each one? > > -- > Message posted via http://www.officekb.com ...

Show This Folder as E-Mail Address Book DIMMED can't check box
Outlook 2003. When I am sending a New Email Message or replying, I must manually type in the To, CC, BCC fields. I can't mouse on the To..., CC..., or BCC... and then select contacts from a list because nothing appears in the "Show Names From the:" drop down list. I assume this is because I must (one at a time) right click on my Contact Folders, Outlook Address Book TAB, and check the box "Show This Folder as E-Mail Address Book". But I can't check the box because it is DIMMED. John "J.J." Jackson You need to install the Outlook Address Book service...

Send As permissions set on all users, need to remove!
Environment: Windows 2003 SP1, Exchange 2003 SP2 Symptoms: All users can "Send As" any and all users Hopeful Resolution: No user is allowed to "Send As" possibly "Send on behalf" but not "As" I have looked at, permissions in all group memberships, individual permissions, Outlook "Delegate" settings, on mail store; and cannot see how this behaviour is happening and it needs to be corrected. Any suggestions for trouble shooting this or tools, fixes patches etc? "Michael P" <michael@michaelpitfield.com> wrote: >Environme...

creating sorted copy of table on second sheet
Hello all, table on sheet 1 consists of (say) a row with names and a row with room numbers and is sorted by name. (First row is header and not included in sorting.) I want to create a copy of this table on a second sheet containing the same data sorted by room number which automatically re-sorts when the data in the first table is changed. I could think of a matrix function but could not find any. Any idea? -- email me: change "nospam" to "w.hennings" Dipl.-Ing.(=M.Sc.Eng.) Wilfried Hennings c./o. Forschungszentrum (Research Center) Juelich GmbH, MUT <http://www.fz-juel...

Named range error
I created a spreasheet with named ranges, then added another worksheet in the file. Some of the fields on the 2nd tab refer to information on the original information and it's causing a number of named range errors. I'd simply go delete them but I can't . . . why not? Help! Thanks. ...

Best chart to use
I have around 25 or so products I want to chart on overall sales. I have used a column chart which is becoming untidy and messy - is there a better way? Alec ...

Filtering record using combo box list
I've created a form with a combox box and a button to search for records in a table. The button is linked to a macro which filters the table, opens and shows the desired record(s) in another form. The 'where' condition in the macro specifies the criteria for filtering, ie. the value selected in the combo box should be equal to a table field. The desired records did come out but the strange thing is that, an extra record will also appear. To my horror, the field which is being used as a criteria has been altered. So I tested out with other values in the combo box and each ti...

Can I assign Tasks unique identifiers?
As part of my monthly report, I need to export my Task List to MS Access where various other employees can create specific forms and reports from the information. There will be months where data may overlap and I do not want duplicates. Since some of the tasks are as often as weekly, duplicates may not be immediately apparent... I was hoping there was a way to have a unique identifier so Access will filter out identical records. Something other than a unique identifier in the subject field? Will the created or modified date field work? -- Diane Poremsky [MVP - Outlook] Author, Teach Yo...

How to set up merge where you hit tab to fill in blanks.
I see the post from May 2006 but did not see an answer that fit. I am a WordPerfect user accustomed to having "keyboard" merge documents as fill-in forms, such as a fax cover sheet, where the boilerplate sheet has "keyboard" fields and when I press "end field," I am taken to each blank line to fill in the to, fax number, file number, etc. I'm having a heckuva time in Word because while I see the "Ask" fields and the "fill-in" files, I don't know what simple button I use to be taken to each field once I set up the boilerp...

Deleting Rows CTRL End to Blank
Say I have a 100 row table and I delete 50 rows, Excel remembers the last position when you navigate using CTRL END. It goes to a blank at Row 100 in the bottom right corner where the spreadsheet was, not to where it is now. Does anyone know how to change this so it goes to Row 50 instead, where the actual table currently ends? Thanks! Shauna Manual you must press the Save button to reset it Shauna With VBA you can do this Sub test() Rows("10:40").Delete ActiveSheet.UsedRange End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Shauna ...

Server error 139: Can't read register
I am running a register using Windows XP with SP2, and I have recently moved the register to the other side of the store. Ever since, I am now getting the error when starting the soposuser.exe ONLY: Server Failure 139: A server failure occurred while attempting to read the register. Now the only change that has happened here is that I physically moved the computer and its peripherals (A Starmicronics tsp100 line printer and an ingenico 6550 magnetic card swiper). The only physical change is from one network switch to another, the register uses a static IP. I can still access ...

can somebody explain in a understandable way this result
Hello! Can somebody explain why I get 1.0000000149011612 when doing this expression calculation ? double d = 0.2f + 0.2f + 0.2f + 0.2f + 0.2f; I know how to fix it that is not the question is about. I now that a double have more then double as many significant number then a float but can't still understand the result. If I instead change the type of d to float I get the expected result of 1.0. I can also change the type of 0.2 to a double which also give the correct result of 1.0 //Tony On 2010-01-20, Tony Johansson <johansson.andersson@telia.com> wrote: >...

Removing an matrix item from Headquarters
I have RMS 1.3 that has three stores and headquarters. I am trying to remove a matrix item from headquarters. When I follow the procedure in the manaul, the item will be removed from the matirx in Headquarters but it does not get removed at the store level even though I issued a worksheet. We have used all of the obvious worksheets and none of them seem to work. Am I doing something wrong or is this a bug? When you say you are trying to remove the item - I am going to guess you mean delete the item - correct? If you are trying to delete an item, regardless of the item type, you need ...

Template- when saved as a new document remove macros
I have a document template and have incorporated several macros (saved in just the document). I'd like to be able to make it so that when the user saves the filled out template as their own document (it'll have a new name of course)the new document has all the macros removed. If the template really is a template and not a document, when new documents are created from the template those documents do not contain the macros. They remain in the template. Resaving existing documents with new names is bad practice and will lead to loss of wanted documents. -- <>>< >...

Can't add ticker symbol GOOG to Money Deluxe 2004
Can you believe it? I can. I receive the following message: "At least one ticker symbol was not added. Try entering the symbol or symbols again. To find the correct symbol, go to the Stocks page, and then click Find Symbol." Well, I did that and GOOG is the correct symbol for Google. Anyway around it as now I can't get stock price updates. Thanks for your help. Lynn In microsoft.public.money, LEClark wrote: >Can you believe it? I can. I receive the following message: >"At least one ticker symbol was not added. Try entering the symbol or >symbols again....

Sort with header rows
Using Excel 2000 I have a table to sort, but the top three rows are all header rows. This prevents me from using the Data | Sort option because the "header row" option only eliminates the first row from the sort. All I know to do is select all the rows in the table that need to be sorted and then use the Data | Sort option and check the "no header row" box. This gets cumbersome when there are hundreds of rows in the table to include in the sort and only three rows in the table to exclude from the sort. Any ideas? Bonnie, Add in an empty column into your data and ta...

how do you use and run xsd.exe
Can not figure out how to use this tool ? Hi George, First of all, I would like to confirm my understanding of your issue. From your description, I understand that you need to know how to use xsd.exe provided by Visual Studio .NET SDK. If there is any misunderstanding, please feel free to let me know. Xsd.exe is the XML Schema Definition tool which generates XML schema or common language runtime classes from XDR, XML, and XSD files, or from classes in a runtime assembly. The file locates at C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin You can run xsd.exe directl...

how do I delete horizontal lines created by using the hyphen key
To type up a signature line by using the hypen /shift key to create a signature line, somehow it gets highlighted and creates line break into my text. I try deleting it by using the deleting key. It just won't delete. How do I get rid of this line? Also tried highlighting it and deleting it. It's a Border on the previous paragraph. To create a line to write on, set a tab stop where you want the line to end, and turn on the Underscore leader in the Format Tabs panel. On Mar 24, 4:39=A0pm, chagui <cha...@discussions.microsoft.com> wrote: > =A0To type up a sig...

Using Money 2004 and auto categorizing downloaded transactions???
I received Money 2004 on a new laptop that I bought. All I want to do with it is download all my account transactions from Bank of America, pull them into Money, and have everything autocategorize based on the name so I can easily see 'where the money goes' , ie mortgage, bills, entertainment, etc. I can't see any easy way to do this. I can download and import data fine from BOA. The problem is that each transaction has a date encoded in the payee such as LOWES 0212 or LOWES 0215 so that EVERY TRANSACTION to the same payee is treated as a different payee. I found some rudiment...

Remove Sales History Question
Have a Great Plains V7.5 system that has never had Sales History removed. Two tables Sales Tax Work (SOP10105) and Sales Distribution Work (SOP10102) each have over 600,000 rows and the application is running very slowly. Decided to run the Utility function Remove Sales History and used a date range to remove all Sales History prior to 1/1/2006. The utility ran forever but appears to have finished okay. However the table row counts for these two tables has not changed and in the system we can still display old sales data under Inquiry > Sales > Transaction by Document for in...

On calendars why can't I see busy/free data and why can't I use OO
When trying to schedule a meeting for anyone in/on my network I can't see busy/free data because it specifies it cannot be retrieved. It only recently started this in the last few weeks. How can that be fixed? Also, this past week I noticed that I can no longer use the Out of Office Assistant. I get a message stating that 'Your out of office settings cannot be displayed, because the server is currently unavailable. Try again later.' As far as I can tell, the server is up and running. Thanks, Jason Version of Outlook and Exchange? Was either recently upgraded? Does everyo...