 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Removing blank rows
I noted a post in January where someone wanted to insert blank rows in every other row of a worksheet I've got the reverse issue. A database created from a text input has a blank row from the original double-spacing - but not every second row. How do I remove blank rows in 50,000 lines of data? I don't know whether he blank rows carry some baggage from the conversion - was a copy out of a text file with space-separated values and double CR between lines (rows). This is for someone wanting a semiconductor data list that can eventually be sorted by column values. Will work as is, but is twice as big as needed. When currently sorted, the blank rows are not shifted out of the tables. RL What happens if you (or that someone) just sorts the data? Don't the "blanks" get sorted to the top or the bottom--or at least grouped together? > I noted a post in January where someone wanted to insert blank rows in > every other row of a worksheet [quoted text clipped - 18 lines] > > RL  SignatureDave Peterson >What happens if you (or that someone) just sorts the data? > >Don't the "blanks" get sorted to the top or the bottom--or at least grouped >together? Actually, it's even worse than I thought. A worksheet won't sort. Tried reversing order (12,000 lines) without effect. Text should at least sort in alpha-numerical order, from my experience. yow RL Are you selecting the range to sort or allowing excel to guess what it should be. If you're not selecting the range first, try that. Excel can often guess wrong--especially with blank rows and/or blank columns. > >What happens if you (or that someone) just sorts the data? > > [quoted text clipped - 12 lines] > > RL SignatureDave Peterson >Are you selecting the range to sort or allowing excel to guess what it should >be. > >If you're not selecting the range first, try that. Excel can often guess >wrong--especially with blank rows and/or blank columns. That seemed to do the trick. With all data selected and specifying a sort by column, the blank lines got ousted without losing row association. Many thanks. RL If the rows do not sort out then you are probably correct in surmising some type of baggage in blank cells. Sub TRIM_EXTRA_SPACES() Dim Cell As Range For Each Cell In Selection If (Not IsEmpty(Cell)) And _ Not IsNumeric(Cell.Value) And _ InStr(Cell.Formula, "=") = 0 _ Then Cell.Value = Application.Trim(Cell.Value) Next End Sub Note: if the baggage is is the non-breaking space character 160 you can try an Edit>Replace what: Alt + 0160 (on numpad) with: nothing replace all When done, F5>Special>Blanks>OK>Edit>Delete>Entire Rows Gord Dibben MS Excel MVP >I noted a post in January where someone wanted to insert blank rows in >every other row of a worksheet [quoted text clipped - 18 lines] > >RL >If the rows do not sort out then you are probably correct in surmising some type >of baggage in blank cells. [quoted text clipped - 21 lines] > >Gord Dibben MS Excel MVP I tried the macro. Got a long wait, but no apparent effect or change in sorting behavior. The edit function can't to find the character, in the sheet or selected lines. RL Hi, Do you really have blank XL rows between you data, or do you have a blank line at the bottom of each XL row? Dave >Hi, >Do you really have blank XL rows between you data, or do you have a blank >line at the bottom of each XL row? >Dave No, it was a new enumerated line, and not grouped or associated with the preceding line. Selecting all of the data on the page and sorting by selected column got rid of the blanks in a trial worksheet. I was used to just having to pick a single column cell to do this previously. 12000 lines are now 6000 without loss of row associations. Thanks for the help. RL
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.