Delete 'non-blank' rows...

Hi. I've pasted data into my worksheet that leaves every other row empty. I 
need to delete the empty rows but sorting messes up the order, 
GoTo/Special/Blank won't work and neither will several DeleteEmptyRow macros 
I've tried.

I applied the ISBLANK function to the empty rows and it returned a FALSE 
value so something unseen is in those rows. No matter, I still need to 
delete them.

Any ideas?

Thanks,

silas 

0
4/11/2007 5:03:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
377 Views

Similar Articles

[PageSpeed] 32

Use autofilter, filter blanks and delete all the rows that get
displayed.

On Apr 11, 10:03 pm, "silas" <silas.NOSPAMprop...@gmail.com> wrote:
> Hi. I've pasted data into my worksheet that leaves every other row empty. I
> need to delete the empty rows but sorting messes up the order,
> GoTo/Special/Blank won't work and neither will several DeleteEmptyRow macros
> I've tried.
>
> I applied the ISBLANK function to the empty rows and it returned a FALSE
> value so something unseen is in those rows. No matter, I still need to
> delete them.
>
> Any ideas?
>
> Thanks,
>
> silas


0
4/11/2007 4:14:14 PM
That did it. Thank you much.

silas


"Rupesh Tripathi" <rupesh.tripathi@gmail.com> wrote in message 
news:1176308054.143247.318200@d57g2000hsg.googlegroups.com...
> Use autofilter, filter blanks and delete all the rows that get
> displayed.
>
> On Apr 11, 10:03 pm, "silas" <silas.NOSPAMprop...@gmail.com> wrote:
>> Hi. I've pasted data into my worksheet that leaves every other row empty. 
>> I
>> need to delete the empty rows but sorting messes up the order,
>> GoTo/Special/Blank won't work and neither will several DeleteEmptyRow 
>> macros
>> I've tried.
>>
>> I applied the ISBLANK function to the empty rows and it returned a FALSE
>> value so something unseen is in those rows. No matter, I still need to
>> delete them.
>>
>> Any ideas?
>>
>> Thanks,
>>
>> silas
>
> 

0
4/11/2007 5:31:30 PM
Reply:

Similar Artilces:

make colour in rows stay
How can I lock the fill colours in a row even when I change around the data or sort alphabetically? Thanks and God Bless, Harley Use CF http://www.cpearson.com/excel/banding.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Crowraine" <Crowraine@discussions.microsoft.com> wrote in message news:ED158D7D-62AB-4DA8-A948-2A6556D8047C@microsoft.com... > How can I lock the fill colours in a row even when I change around the data > or sort alphabetically? > > Thanks and God Bless, > Harley ...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Deleting "mail to:" email address links
I have a relativly straight forward spreadsheet where I am maintaining a large address list. It contains a homeowner's lot number, name, address, telephone #, and e- mail address. When attempting to select random cells for data entry/modification, I receive and automated message " mailto: (a random e-mail address) - click once to follow. Click and hold to select this cell." I have no idea where/how this message/feature/function is being attached to these cells but would like to eliminate it. Since seeing these messages, the size of the worksheet has been increasing in ...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Cannot delete report
Hi All, I have install the report manager sometime ago, it is working fine. Until recently we found that when i click the action menu, nothing is come out. I can't delete or rename the report. But I can add new report. Do anyone encouter this before? Thanks. -- Regards, Venedict ...

Categories deleted converting from Quicken
Hi folks, I just converted from Q2003 Premier to the Money 2005 Trial 2 days ago. I noticed today that a number of categories have been removed from the transactions in my checking account and credit card accounts. What I've noticed so far: 1. All sub-accounts for Utilities were gone. This wasn't too bad because I could use find replace since the payee name has been the same on these for all years I have in Quicken/Money. 2. All sub-accounts under Insurance were gone. 3. The category Groceries came over, but there were no transactions in it. Quicken had several hundred...

Create individual files from a row
I have an excel file with several thousand entries, which contain data in several columns. I would like to be able to create an individual xml or html file for each row, but with predifined formatting around so Mr A bloggs, A street, A town, AA1 1AA Could become Abloggs.html <head></head> <name>Mr A bloggs</name> <street>A street</street> Any other info here as well </html> etc. Is this possible and any suggestions how? thanks, Graham. -- GrahamN ------------------------------------------------------------------------ GrahamN's Profile: ...

Add a blank page separator
How do I add a blank page automatically as a separator after every 15 pages when printing a document of 250 pages? Use a macro containing the following code to print the document: Dim i As Long Dim blank As Document For i = 1 To 240 Step 15 ActiveDocument.PrintOut Range:=wdPrintFromTo, From:=i, To:=i + 15 Set blank = Documents.Add With blank .PrintOut .Close wdDoNotSaveChanges End With Next i -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professio...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Need to retrieve a deleted Excel File
On Saturday I hide an excel sheet and then protected the workbook. When I got into work today to open the file it was non-existent. What do I have to do to retrieve the file? Does it make a difference if the file extension ended with .csv? Thanks in advance! Search in Windows to make sure it's still on your compute -or go to the folder the file is in and look to make sure it's still ther -or If you didn't do this, you might need to to text, which includes *.cs Excel is not set to open this type of file by defaul tj Did you (try to) open the file with Excel. If so : did ...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Securities with closed positions are being deleted
I am using Money 2006 Deluxe--having upgraded from the 2003 Money Business version. It appears that my closed security positions are being deleted automatically 60 days after selling the last shares. Price history for these securities is also deleted. I believe that in Money 2003 (Business) that I used a setting that is causing this (the results, however, were not my intent). Money 2006 does not appear to have such a setting. How can I stop this from happening now for securities that I will be selling? How can I recover the "lost data" How can I force Money 2006 to pic...

underline in row not repeating
It seems as if my bottom of the cell outline is not repeating on subsequent pages. I have a few header rows on a sheet. The bottom row has a bottom line. I'm talking about cell outlines, not underlining of words in the cell. Anyway, I've told the print option to repeat the first 3 rows on following pages but the bottom outline does not seem to repeat. How can I fix this? Thanks, Keith Never mind. This seems to be working. Not sure what was going on the first time. "Keith G Hicks" <krh@comcast.net> wrote in message news:OM82rj8rIHA.2068@TK2MSFTNGP05.phx.gbl... >...

Deleted calendar from Public Folders
Accidentally I deleted Calendar in Public Folders on the Exchange 2000. Is there a way to bring it back? I tried to locate an article in Knowlege Base searching with "Recovering deleted items from a public folder", but the article is gone. On Mon, 3 Apr 2006 21:28:01 -0700, Daniel <Daniel@discussions.microsoft.com> wrote: >Accidentally I deleted Calendar in Public Folders on the Exchange 2000. Is >there a way to bring it back? >I tried to locate an article in Knowlege Base searching with "Recovering >deleted items from a public folder", but the a...

deleted items #7
Does anyone know what and how I can view and/or delete the 1129 items my Outlook says I have in my deleted items folder. 1 says unread. Thanks If can see the Deleted Items folder in the folder list, right-clicking on it reveals "Empty Deleted Items folder" If you can't see the folder list, it's under the 'Go' menu in Outlook 2003, and under the 'View' menu in earlier versions You can also empty the Deleted Items folder automatically every time you close Outlook. Tools menu > Options > 'Other' tab. Choose the 'Empty the Deleted Items f...

How to unhide Excel rows after they are automatily hidden
Helpful advice on posting to newsgroups here... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Chuck" wrote in message ...

Calendaring agent failed in message delete notification
We have a user who previously synchronised his calendar with a psion and has many messages that had reminders on that he now can't delete using Outlook (gets error) but he keeps on being reminded that they are late. The psion has been thrown away so how can I delete these messages? Assuming we back up his calendar can we reinitialise it? Would a mailbox move help? Thanks David Event Type: Error Event Source: EXCDO Event Category: General Event ID: 8200 Date: 29/11/2004 Time: 10:08:33 User: N/A Computer: MAIL1 Description: Calendaring agent failed in message delete notification with err...

Non AD emails going to 1 user
I have an Exchange 2003 server running on SBS 2003 the issues is one user is getting all the emails sent to him that look like they are coming from his domain. For example his email is user@mydomain.com but in his inbox he is getting XYZ@mydomain.com but XYZ is not in the AD or has a email box set up on this server. Why is getting this non AD email and how can I stop it. Thanks in advance Are you sure it's not a SPAM where the spammer may have simply put in 123abc@mydomain.com and BCC it to all possible conceivable names @mydomain.com?? R Green "LaOVis" <LaOVis@discuss...

Copying of non-consecutive, columnar data into consecutive-columnar cells
Hi: I have data located at a1:a9, then a:10 is blank; then, I have 9 more lines of data from a11:a19, and a20 is blank... a1, a11, a21...are all the texts/numbers that I want printed on a different page. But here's the catch: I'd like to print it at locations a1, a2, a3... Specifically, the data that I have appears like this on THIS IS ON SHEET 1: A1: prefix: Mr A2: emailAddress: john.smith@1.com A3: firstName: John A4: lastName: Smith A5: company: ABC Company A6: A7: prefix: Mrs A8: emailAddress: kim.davids@2.com A9: firstName: Kim A10: lastName: Davids A11: company: X...

Deletion settings in Mailbox Store Properties
Our company would like to enforce a policy to permanently delete items 30 days or older from each mailboxes Deleted items folder. The mailboxes all reside on the Exchange 2003 server. Under Mailbox Store Properties, Deletion settings, the Keep deleted items for (days) is set to 30, yet the items are not being deleted. My thoughts are that it is because we do not have the Mailbox Manager scheduled to run. Is that correct? I am a little hesitant to run it because I am worried about what else it might "manage". I only see one recipient policy and that is for email addresses. ...

EFS
Hi, I did something bad and enabled EFS on a folder, then decided to remove EFS. Windows has now left me with a number of EFS*.tmp files which I cannot now delete and they are really large. I understand what these files are, but really want to delete them and so far I haven't found a way to do this, have tried using Admin privilages and a Google search was fruitless. Help :-) Thanks "David" <amharcAE@ice.age.fastmail.fm> wrote in message news:5fkf165079mj6sq3jl4jui8f7fveennejo@4ax.com... > Hi, > > I did something bad and enabled EFS on a ...

Deleted Exchange accounts reappear
We seem to have an issue where a deleted mailbox will sometimes reappear. These mailboxes are on Exchange 2000 or 2003. Generally, when an Exchange account is deleted, the users AD account is disabled, but left on the server for three months and then the account is deleted. However we have had a few delted mailboxes repopulate on some of these accounts. Has anyone seen such an issue? And if so, how can it be resolved. ...

Help with Highlighting all duplicates in a row
I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format->Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE) Where A5 is the first cell in Range1....