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
     With 
ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Simon\newratings.txt", _
         Destination:=Range("A2"))
         .Name = "newratings"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .TextFilePromptOnRefresh = False
         .TextFilePlatform = 850
         .TextFileStartRow = 1
         .TextFileParseType = xlDelimited
         .TextFileTextQualifier = xlTextQualifierDoubleQuote
         .TextFileConsecutiveDelimiter = False
         .TextFileTabDelimiter = True
         .TextFileSemicolonDelimiter = False
         .TextFileCommaDelimiter = True
         .TextFileSpaceDelimiter = False
         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
         .TextFileTrailingMinusNumbers = True
         .Refresh BackgroundQuery:=False
     End With
     Range("K3").Select
     ActiveCell.FormulaR1C1 = 
"=IF(ISNUMBER(--(LEFT(RC[-10]))),RC[-10],R[-2]C)"
     Range("K3").Select
     Selection.AutoFill Destination:=Range("K3:K601"), Type:=xlFillDefault
     Range("K3:K601").Select
     Selection.Copy
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
         :=False, Transpose:=False
     Rows("3:601").Select
     Application.CutCopyMode = False
     Selection.Sort Key1:=Range("K3"), Order1:=xlAscending, 
Header:=xlGuess, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortNormal
     Columns("K:K").Select
     Selection.Delete Shift:=xlToLeft
     ActiveWindow.SmallScroll Down:=-15
     For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If IsNumeric(Left(Cells(i, 1), 1)) Then Rows(i).Insert
Next i
     Range("A2").Select
End Sub

0
5/17/2009 9:44:12 PM
excel 39879 articles. 2 followers. Follow

0 Replies
779 Views

Similar Articles

[PageSpeed] 25

Reply:

Similar Artilces:

Landed Costs ability to apply different rates per line item
Currently on the Landed Cost screen, you can enter a landed cost amount and it will be applied to all items being received on that receiving transactions. However there are times that one needs to apply a different rate for each line item and at this time it appears the only means to do this is to receive each line item seperately so a different landed cost can be applied. Therefore, this is not time productive for our users. We should be able to apply a different rate per each line item and receive all line items at the same time. ---------------- This post is a suggestion for Micr...

Send mail on behalf of UserX with UserX's mailbox as extra mailbox
Hi, Running Exchange and Outlook 2000 I need a solution for this scenario. I have some users A and B who have UserX's mailbox configured as a extra mailbox in their Outlook client, so they can respond to mail being recieved by UserX (i.e. a helpdesk account). These same users A and B need to send mail "on behalf of" UserX so our disclaimer software (eXclaimer) adds users A and B signature to email being send by UserX's mail address. In this scenario "Send as" will add UserX's signature, not users A or B's signature. Assigning only "Send on behal...

To Field blank in Sent Items folder
Hi there One of our users is having a problem in that when she sends an email, it goes to Sent Items and the To field is blank. You can't see names in the column, nor if you open an email and check it. We have 60 others users with the exact same spec. machine and same patch level, they don't have this problem. Has anyone experienced this behaviour before and if so how can it be remedied? Office 2003 latest updates and service pack Windows XP Pro latest updates and service pack Regards Dave Hi Dave, Is she using a PST at all? You could run a ScanPST. Alternatively you coul...

Help!!! Pivot table from mac goes totally blank on pc.
I'm trying to port some spreads from a mac to a pc. There are pivot tables on the spreads that refresh fine on mac Excel 2004. But when refreshed using Excel 2003 on windows, the data in all the pivot tables goes totally blank. Is this a known bug, or should it work? Any suggestions as to how do debug the problem? Thanks! ...

Extra space between paragraphs when sending
Outlook 2007 adds extra “line feeds” between paragraphs on all outgoing messages. I can’t seem to stop it and it is slowly driving me mad. People getting my e-mails must think I can’t write. I have checked (“don’t add extra space…”) in the style formats. Doesn’t help. The composed messages look fine. They get changed when they are sent. Sometimes happens on incoming mail. I would go back to Office 2003 but too many file extensions are now different. If I can’t fix this I may go to a server based mail system. Why MS made this change is beyond me. It’s in the same category as c...

Company List Blank in Workflow Administration
Hi, I have been trying to get GP10 Workflow up and running and run into problems. The setup went through fine and apparently installed successfully - however, when you go in to Workflow Administration the Company downdown listbox is blank. Activating Workflow in a company doesn't seem to make any difference. Any ideas? -- Andrew Cooper ...

Next line in CrichEditCtrl
Hi all.. I am new to VC++. i am doing the following task and am stuck up at a point. task: I created an SDI. when i open a file(FIle->Open), I want to get the file displayed with line numbers and when i close it, it should be the same file again. I did it in CEditView using archive and Getfile() functions but it seemed to be a crude way. I tried it using CRichEditView but i realised that always my CRichEditCtrl is pointing to the first line of the view when i open a file. can someone tell me a way in which i can get the starting location of each line using CRichEditCtrl?? T...

how to find relative posiition of first and last non-blank cell in a range?
Hello, I have a rows of data in which I want to get, for each row, th position (not the value) of the first and last non-blank cell in th row. Basically i'm looking at hours worked by employees (employees are i rows) and columns have the week (week 1 , week 2 , week 3). I want t get the start and end duration of each employee. so if employee A works 0 hours in week 1 , 3 hours for week 2, 0 hour for weeks 3-5, 4 hours for week 6 and zero hours for weeks 7-10: I wan to get the value of 2 for start duration (first non-zero value occur in week 2), and I want to get value of 6 for end dur...

Outlook Rule to send subject line only
Hello, I would like to have Outlook redirect just the subject line (not the message body or header info) of incoming messages to my mobile phone (SMS) e-mail address. Could anyone suggest a rule that would accomplish this? Thanks in advance, at you'll need an sms addin that can control how much is sent. http://www.slipstick.com/addins/pager.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlo...

represent blank cell
what is the best way to represent a blank cell? I can't use N/A or "" Thank -- maluguitaris ----------------------------------------------------------------------- maluguitarist's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1481 View this thread: http://www.excelforum.com/showthread.php?threadid=27224 Hi there's no way to represent a real blank cell as result of a worksheet formula. "" and NA() are the best you can achieve For what do you need a blank ell? -- Regards Frank Kabel Frankfurt, Germany maluguitarist wrote: > wh...

Line distribution
In Visio 2003, how do I equally distribute lines drawn with Connector Tool? Visio's Distribute feature works with any type of shape including connectors. However, if your connectors are glued to a shape, they are ignored. Select the shapes to distribute and go to Shape > Distribute Shapes. -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Ekim Snehctik" <Ekim Snehctik@discussions.microsoft.com> wrote in message news:B8B2944E-9837-4387-A7D1-7C1DB064966F@microsoft.com... > In Visio 2003...

How to print a blank form for data-entry purpose?
I want to print a blank form for data entry purpose. If the form is totally blank, nothing prints out (except for label on top of form). To solve this problem I input a simple character in one field, which is not a wise solution. Is there a better way to print blank form? And more, in numerical fields, a zero appears. I want this zero not to appear in print. How can do this? Rasoul Khoshravan wrote: > I want to print a blank form for data entry purpose. If the form is > totally blank, nothing prints out (except for label on top of form). > To solve this problem I input a simple cha...

Extra field for Items
Does anyone have an addin for extra user defined fields for items? -- Anthony Anthony, We could easily deliver this to you but we do not have anything pre-made. If you are interested please contact us at 1-888-267-RITE or drop us an email at sales@rite.us -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230-2282 ext. 4002 (Office) 320-230-1796 (Fax) "Anthony Novielli" <AnthonyNovielli@discussions.microsoft.com> wrote in message news:76CE74F3-5716-4520-9883-6D07C7670143@microsoft.com... > D...

One regression line
Hi, I have two series but I want one (common) regression line for both, not one for each. Is that possible? Hi, In two helper columns, copy the x- and y- data of the first data-set; now copy the second data-set and paste it below the first data-set in those helper columns. Make an xy-scatter plot for the combined data (helper columns) and obtain the trendline. If you prefer to have the two sets plotted as separate series, maybe for other purposes, format Series 1 (the combined series) with "None" for "Line" and "None" for "Marker" (This actio...

some subforms and combos blank
When I open the fClosure form from a button on the f001ProjectReview form. It opens the fClosure form but some of the comboboxs and some of the subforms are blank. As in totally white. any ideas of why? If I open the fClosure form without using the button on the f001ProjectReview form it works fine. Here is the button code I use to open form Dim stDocName As String Dim stLinkCriteria As String stDocName = "fClosure" stLinkCriteria = "[ProjectID]=" & Me![ProjectID] DoCmd.OpenForm stDocName, , , stLinkCriteria DoCmd.Close ...

bottom line in word document fails to print regardless of margins
whenever i print a word document the last line does not print....no matter what the margins are, whatever I select whatever I do...it does not print...can anyone help Some inkjet printers will print only to a certain point. You can also check to see if you have the right paper for the software settings (Letter size, Letter paper - or A4 size, A4 paper, etc). "jerry" wrote: > whenever i print a word document the last line does not print....no matter > what the margins are, whatever I select whatever I do...it does not > print...can anyone help Try goin...

Change Item properties "Detail Line 1" to text like "Color" or "Si
In the Item properties window of POS 2.0, fields such as "Detail Line 1", "Detail Line 2" and "Detail Line 3" cannot be changed to some other text like "Color", "Size" or "Material" etc. By providing such ability would help customers not to remember what goes in which field thus eleminating errors while entering an item. I have learned that it can be done in RMS 2.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the &q...

Blank Message Body after KB842436
NT4, SP6a, IE6 with updates Exchange Server 5.5 SP4 Just download and installed KB842436, which is a security update for Exchange Server 5.5 released on 8/10/2004. (reference: http://www.microsoft.com/downloads/details.aspx?FamilyId=66E4E033-5A4C-4EEC-84F1-31F0CA878092&displaylang=en) Following the installation of the update, I can no longer see the message bodys when using Outlook Web Access. The readme.txt file that accompanied the install files mentions that this is a possibility and refers me to KB314532. http://support.microsoft.com/default.aspx?scid=kb;en-us;314532 I have tried t...

An extra Row Source Field needed
SELECT tblOwnerInfo.OwnerID, IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ') & IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' ') AS OwnerName FROM tblOwnerInfo ORDER BY IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ') & IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & ' '); If tblOwnerInfo.Email has some value in it, add "@" After tblOwnerInfo.OwnerLastName ie Gates @ Bill Thanks f...

Increasing the Vertial Size of a 3-D Line Chart
Hi Everyone I have a chart that looks good if I've only got a few items on the category (x) axis (for days). However when I include more days the height of the chart decreases leaving a lot of white space at the top and bottom of the chart. I have increased the chart size but this only helps a bit. I think what I need to do is somehow change the feature that automatically sets the vertical scale so I can set it to manual. I realise that this will change the relationship between the X and Y axis but this is what I want to do. Hope this make sense and someone can help Thank you Ma...

Extra Extra Pages
I have finished my word document and saved it. When I open it back up to preview before I send it, it shows an extra page between my body of work and reference page. I don't want to send that to my professor. Can anyone help me? Thanks Dennis -- Dennis Jackson See http://sbarnhill.mvps.org/WordFAQs/BlankPage.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Dennis Jackson" <Dennis.Jackson.60bcd11@wordbanter.com> wrote in message news:Dennis.Jackson.60bcd11@wordbanter.com... > ...

Extra line space
I have run into the problem several times, where I have several lines of words in seperate merged cells and it appears to be spaced correctly, but when I print there is an extra line space in the document. Is there some way to correct this? I have the row height so it matches the amount of space needed, but it is adding an extra line on the printed version..... The only fix I know of for this is to manually adjust something to make it look nice--either columnwidth or rowheight. And I don't think fixing it for your specific printer is any assurance that it'll be fixed for the pers...

TRIM en masse deletion of blanks
Hi, I am trying to remove one (or more) black spaces before words in a spreadsheet. I can use the =TRIM() function okay to remove the first blank, but want to use the resulting column in a mail merge exercise. As (I think) you need to insert another column to cater for this formula, I cannot seem to get this to work in mail merge. Any help would be appreciated. Thanks David. David McRitchie has some code that will help clean spaces (and non-breaking spaces from HTML): http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") DGodden wrote: > > H...

Message title is blank
Using Outlook 2000, I have noted lately that when I reply to an email stored in my pst file, the information does not have a subject line or a message text from me as it supposed to have. It is just an envelope that is blank. Any idea what is causing this behavior and how to resolve this? It does not happen from my mailbox itself but only from my pst files that are stored on the network. ...

Resizing a Line Chart
I have a line chart with three months of data spanning across 17 series. The chart has no legend, but a Data Table with Legend Keys turned on. Due to there being 17 series & the data table turned on the chart is quite tall. I would like to resize the plot area making it a bit squattier. Every time I try to resize it using the upper center handle it shrinks it, but it also pulls the bottom up which crops off the lower items in the data table. Is there a way to "squish" the plot area virtically without adjusting the data table? You might try eliminating the built-in data ta...