extra empty rows in Excel file

My Excel file has 950 rows of data.  Somehow, I now have 
over 58,000 rows of empty cells; the file size has grown 
from 500kb to 2100 kb.  How do I delete the empty rows?
0
anonymous (74717)
6/16/2004 12:39:54 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
266 Views

Similar Articles

[PageSpeed] 36

Using vba you can try something like:

Sub DeleteRows

Dim Row as integer
Dim Col as integer
Dim CountingValue as integer

For row = 1 to 60000
Col = 1
For col = 1 to 10
if len(cells(row,col).value) > 0 then 
CountingValue = CountingValue+1
goto nextRow
end if
Next Col
NextRow:
if Countingvalue >0 then
Cells(row,1).entirerow.delete
Countingvalue = 0
end if
Next Row

end sub


The script checks the first 10 cells of each row. If all of them ar
empty, the row is presumed empty and is deleted. If one or more of the
contain any data, the row is not deleted

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

0
6/16/2004 1:10:20 PM
Hi

Have a look here:
http://www.contextures.com/xlfaqApp.html#Unused

-- 
Andy.


"Linda" <anonymous@discussions.microsoft.com> wrote in message
news:1cee101c4539f$067fc9c0$a301280a@phx.gbl...
> My Excel file has 950 rows of data.  Somehow, I now have
> over 58,000 rows of empty cells; the file size has grown
> from 500kb to 2100 kb.  How do I delete the empty rows?


0
andyb1 (494)
6/16/2004 1:30:31 PM
>-----Original Message-----
>Using vba you can try something like:
>
>Sub DeleteRows
>
>Dim Row as integer
>Dim Col as integer
>Dim CountingValue as integer
>
>For row = 1 to 60000
>Col = 1
>For col = 1 to 10
>if len(cells(row,col).value) > 0 then 
>CountingValue = CountingValue+1
>goto nextRow
>end if
>Next Col
>NextRow:
>if Countingvalue >0 then
>Cells(row,1).entirerow.delete
>Countingvalue = 0
>end if
>Next Row
>
>end sub
>
>
>The script checks the first 10 cells of each row. If all 
of them are
>empty, the row is presumed empty and is deleted. If one 
or more of them
>contain any data, the row is not deleted.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>


THanks; I'm not familiar with using VBA.  Is there another 
method?
0
anonymous (74717)
6/16/2004 3:48:11 PM
Linda

Select a sheet.

Hit CRTL + END keys.

Where does this take you?

If far below and to right of your real data range, select all rows below your
data and Edit>Delete.

Do the same for columns to the right.

Follow these steps for each sheet.

NOW IMPORTANT STEP.....Save your workbook.  Close and re-open.  Your used
range should now be much smaller.

Gord Dibben Excel MVP

On Wed, 16 Jun 2004 08:48:11 -0700, "Linda"
<anonymous@discussions.microsoft.com> wrote:

>THanks; I'm not familiar with using VBA.  Is there another 
>method?

0
Gord
6/16/2004 9:55:02 PM
Reply:

Similar Artilces:

Win32 Hook, Capture Menu Text From MS Word, Outlook,Excel...
Hi All, I have made win32 hook for following hookKeyBoard = SetWindowsHookEx(WH_KEYBOARD,(HOOKPROC)KeyboardHook,hInst,0); hookMessages = SetWindowsHookEx(WH_GETMESSAGE,(HOOKPROC)MessageHook,hInst,0); hookCallWndMsg=SetWindowsHookEx(WH_CALLWNDPROCRET,(HOOKPROC)CallWndMessageHook,hInst,0); By kbrd hook i caprure Ctrl+c,v,x With no problem. By GetMessage hook i can monitor messages posted to a message queue. such as WM_COPY, PASTE, ... ( other clipboard msg ) By CALLWNDPROCRET I need to capture Menu Caption of other application. In that I am look in for WM_MENUSELECT message and form lowor...

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...

excel problem #5
hi i am using excel 97 english to write four files and then copied it to hp 3715 ppc which is using pocket excel 2002 chinese. i read it through hp 3715. i do not change it anything. the question is when i copied it to my computer and then i am using excel 97 to open, i cannot open it. i found that the file extension is pxl so that i change it to xls and then open it again, the sheet is empty. please help me thanks jimmy Is this a translation problem? - in that the Pocket Excel 2002 Chinese can read English but writes Chinese, and that the .pxl file (written in Chinese) is not unde...

Excel is adding 2 rows of data source together when charting
I'm trying to create a simple chart that shows trending during the year. I have several rows of data -- 1/2 of them being actual financial performance numbers and the other 1/2 being budget numbers. When I select the data ranges, whether using the Control Key to select all ranges at once, OR by selecting them individually, some of my data ranges are being added together, resulting in the ranges NOT reflecting what the source table numbers are. The ranges appear to be correct. I've tried crafting each range individually with the same result. Why is this happening? Wh...

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...

can't open my money2001 file
I have a strange problem with Money2001. I can't open my files. It asks for my password and after I enter it, a box pops up saying, "Microsoft Money has encountered a problem and needs to close. We are sorry for the inconvenience." I've tried opening some backups that I have achirved from clear back from September - still no luck. Any suggestions? If I buy the latest version, what are the chances it will open my old 2001 version file? Aaron In microsoft.public.money, Aaron Garber wrote: >I have a strange problem with Money2001. > >I can't open my ...

excel97 files
What program will use and/or convert excel97 *xls files? Don To what? All later excel versions can open excel files made in previous versions, and with regards as to 97 it is still the same file format that it's being used -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Don Cromer" <DonCromer@comcast.net> wrote in message news:CuCdnUgCF6mftIvZRVn-uQ@comcast.com... > What program will use and/or convert excel97 *xls files? > > Don > > Sorry, I panicked. Some year...

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...

Online banking trashes my Money File
Hi. This started happening last week. When I connect to the internet and Money 2004 is either in the process of downloading my (many) active statements it abends with the "Send Error Report" dialog box. When I restart, I sometimes have to go through a second crash. When I eventually view my accounts, I'm told I have statements to read but they aren't bolded, so I can't see what they are. I do a file repair, and I crash when I try to view my statements. It seems the only way to continue is to restore from a backup file and completely eliminate background backing. Any sugg...

Basic Excel Formula Question
Good day. I am having problems in getting a formula to work and though maybe you may see the problem. I have 3 seperate ranges to look at before giving 1 of 3 answers. Simply if "cell " is between "number" and "number" then "2" else if "cell" i between "number" and "number" then "3" else if "cell" is betwee "number" and "number" then "4" end if I just didnt see how to use a range or between these numbers. The "cell" is the number I will put in. and "number wi...

In excell 2003, increasing number of items shown in drop down list
I am trying to increase the number of lines to be displayed in the drop down list, using MS office 2003, excell. I have read contributions to the question possed on 8/11/2007 8:00 PM , but sadly it does not provide the "Format Control" that should enable me to change the mnumber of dropdown lines to be shown. I have 30 items on my soure list and only 12 appear in the dropdown list. How and where can I adjust the figure that dictates the lines to be shown? How do I go about it as I do not see any options leading to the data validation combo box in this version? T...

How do I lock a row in place so that when I sort the worksheet, t.
How do I lock a row in place so that when I sort the worksheet, the row stays at its location, and the other rows sort around it? I believe you can use Data/Filter. Ideally there is already some column in the worksheet that will show all of the records but that row (maybe a helper column would be necessary). Then sort the data and set the filter to show all records. "primenumber" <primenumber@discussions.microsoft.com> wrote in message news:4CD4DE06-48C8-4650-9190-E42BB641A689@microsoft.com... > How do I lock a row in place so that when I sort the worksheet, the r...

Empty to Field on Sent Email
We are experiencing some issues after upgrading from Office 2000 to 2003. Some sent emails have an empty to field. The emails are being delivered ok & we are not receiving any NDR's A quick search on google and quite a few people are experiencing this problem but I can't see a fix. -- Niaz Niaz <usenetkhan@gmail.com> wrote: > We are experiencing some issues after upgrading from Office 2000 to > 2003. Some sent emails have an empty to field. > > The emails are being delivered ok & we are not receiving any NDR's > > A quick search on google an...

Automatically vary range in graph depending on #rows data entered?
I want to set up a chart so that the range of values entered in the Source Data dialog box automatically changes depending on a count of the number of rows of data that has been pasted into worksheet of source data. Can I embed a link to a cell with the number representing the number of rows. For example, in =Data!$H$2:$H$70, I want the "70" to vary depending on how many rows of data have been pasted. For Excel 2002 and/or2007. Dynamic Charts: http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/ http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Pel...

How to send a file with HTTPConnection to a web form?
=20=20Hello, =20=20I've=20been=20building=20an=20application=20to=20access=20some of=20our=20CGI-based=20web=20forms.=20=20Thus=20far=20I've=20had=20little trouble=20controlling=20all=20activity=20that=20our=20web-site provides.=20=20That=20changed=20this=20week=20when=20I=20needed=20to add=20the=20ability=20of=20my=20client=20to=20send=20a=20file=20along with=20the=20request.=20=20I've=20not=20been=20able=20to=20find=20an example=20of=20how=20this=20should=20be=20done. =20=20My=20client=20application=20uses=20CInternetSession, CHttpConnection,=20and=20CHttpFile=20to=20build=20a=...

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...

Corrupt backup file
I get an error message that says "c:\xxx.mbf is corrupted or is not a Money backup file". Is there any hope to recovering the file? rwd In microsoft.public.money, rwdykhuis@gmail.com wrote: >I get an error message that says "c:\xxx.mbf is corrupted or is not a >Money backup file". Is there any hope to recovering the file? > > No. Search your computer for other *.mbf, *.mny files. On Jun 30, 7:52 pm, Cal Learner-- MVP <via_newsgr...@please.tnx> wrote: > In microsoft.public.money, rwdykh...@gmail.com wrote: > >I get an error message that say...

Formating Row and Col headers
How can I change the format and color for Excel Row and Col. Headers? Right-click an empty spot on your desktop, then AppearanceTab > Scheme:....choose and apply, (default is Windows Standard, if you want to get back to normal) Vaya con Dios, Chuck, CABGx3 "Bob Spencer" <bob@k2e.com> wrote in message news:e9lBNhcbFHA.2984@TK2MSFTNGP15.phx.gbl... > How can I change the format and color for Excel Row and Col. Headers? > > ...

How do I create a quadrant analysis in excel?
I am trying to create a quadrant analysis for menu engineering. I want to be able to create a chart with volume on one axis and gross profit dollars on the other against the background of 4 quadrants. Any ideas? XY chart? -- David Biddulph "BH185002" <BH185002@discussions.microsoft.com> wrote in message news:E4EC3C05-ADBF-4808-9E56-BCE200876F07@microsoft.com... >I am trying to create a quadrant analysis for menu engineering. I want to >be > able to create a chart with volume on one axis and gross profit dollars on > the other against the background of 4 qu...

excel autoformat
Why does Autoformat in 3D just turn my data range into a blob of grey with an occassional "shadowed" row instead of each row being "shadowed" as in the dialogue box example??? 3Deffect1 gives a solid blob on my Excel 2002. 3Deffect2 gives a two by two row effect with two raised and two flat. Which are you using? Gord Dibben Excel MVP On Tue, 11 Jan 2005 10:23:16 -0800, "J Norris" <JNorris@discussions.microsoft.com> wrote: >Why does Autoformat in 3D just turn my data range into a blob of grey with an >occassional "shadowed" row inste...

Comapring rows of a table to multple rows within the same table
Hi, I have the following data which I have been tasked with identifying a primary case for each customer. Unfortunately the data contains duplicate records such as Case 1 and 2. I would be extremely grateful if you could tell me how can I compare all the rows related to Customer 1 and flag a case as the primary case. CaseID, CustomerID, PrimaryStatus, SecondaryStatus, GroupA, GroupB 1,1,New, Unassigned, RU, HK 2,1,New, Unassigned, RU, HK 3,1,In Progress, Normal Review, RU, HK 4,1,New, Unassigned, IN, UK 5,2,New, Assigned, JP, 6,2,In Progress, Special Review, BR, HK 7,2,Co...

Excel or Access?
I have server data that is about 200-300 rows/records and includes abou 25 columns/fields. Coworkers have suggested that this data shoul reside in a database, such as Access, and then import/link to it fro Excel for reporting purposes. Does anyone have a recommendation/comment/suggestion on this? I a comfortable using Excel as my "database" for this data that will no grow much larger than it already is, and is easier for me to perfor the reporting in Excel simply on another sheet. Thanks! Ric -- Message posted from http://www.ExcelForum.com Hi, Why fix what's not broken? ...

using vb for copying and inserting rows and formulae
I earlier posted the followin message: >I have tried to create a macro that will insert a new row >into a user specified position on worksheet 1, and then >the macro then inserts a new row into the same row number >on worksheets 2-6 >So if i have highlighted row 5 on worksheet 1 and then run >the macro, a new row is inserted on worksheet 1 and then >also the same happens, in the same position, on the >following 5 worksheets. > >My problem arises in the fact that i want the formulae i >have in the cells above(or below - it makes no difference) >the...

Automatic file associasion, (with no install)
Hi, Is there a way/default or behaviour that would enable me to associate a file extension with a program without installing anything? The extension would of course be totally new and not replace existing ones, the program would be in the same folder, (probably). I want to transport the files on my USB key from PC to PC but I don't want to install the app everytime. And the default windows dialog box asking the user to choose an app is a bit ugly and time consuming. Maybe something like an Autorun.inf setting would allow me to do that? But then the user can disable the Autorun. An...

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... > ...