Removing Unwanted Cells

Hi all,

I've inhereted a spreadsheet off a colleague, and there are LOTS of
empty rows with formatting but no information (the rows with data go
down to 240, but I actually have around 4,000 rows!).

I've been able to remove the formatting, but actually reducing the
number of rows is another thing all together. I've tried simly going to
the bottom, selecting the empty rows and *Right-Click*>Delete, and also
*Right-Click*>Clear Contents. Neither of which seem to work.

This is really a problem of aesthetics, it would just be nice to lean
up the spreadsheet a bit.

Any ideas?

TIA,

SamuelT


-- 
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501
View this thread: http://www.excelforum.com/showthread.php?threadid=479727

0
10/27/2005 9:28:59 AM
excel 39879 articles. 2 followers. Follow

3 Replies
336 Views

Similar Articles

[PageSpeed] 47

Hi Samuel

Try going to the row after the last row of data that you require to keep.
Click on the row number to highlight the row.
Hold down Ctrl+Shift and press the down arrow, and this should select all of 
the rows down to 65536.
Right click>Delete.
Save the file, close and re-open.

If that doesn't work post back, as there is probably some VBA code somewhere 
setting the formatting.

Regards

Roger Govier


SamuelT wrote:
> Hi all,
> 
> I've inhereted a spreadsheet off a colleague, and there are LOTS of
> empty rows with formatting but no information (the rows with data go
> down to 240, but I actually have around 4,000 rows!).
> 
> I've been able to remove the formatting, but actually reducing the
> number of rows is another thing all together. I've tried simly going to
> the bottom, selecting the empty rows and *Right-Click*>Delete, and also
> *Right-Click*>Clear Contents. Neither of which seem to work.
> 
> This is really a problem of aesthetics, it would just be nice to lean
> up the spreadsheet a bit.
> 
> Any ideas?
> 
> TIA,
> 
> SamuelT
> 
> 
0
roger1272 (620)
10/27/2005 10:23:31 AM
Selecting and deleting the empty rows should sort the problem. That said, I 
have a sheet where I can not delete some empty columns at the end. I even 
tried selecting more columns beyond the used range, but that just increased 
the last column, rather than reducing it.

If it's a fairly straightforward sheet, try selecting the cells you want and 
copy them to a new sheet.

-- 
Ian
--
"SamuelT" <SamuelT.1xk2eb_1130407505.3422@excelforum-nospam.com> wrote in 
message news:SamuelT.1xk2eb_1130407505.3422@excelforum-nospam.com...
>
> Hi all,
>
> I've inhereted a spreadsheet off a colleague, and there are LOTS of
> empty rows with formatting but no information (the rows with data go
> down to 240, but I actually have around 4,000 rows!).
>
> I've been able to remove the formatting, but actually reducing the
> number of rows is another thing all together. I've tried simly going to
> the bottom, selecting the empty rows and *Right-Click*>Delete, and also
> *Right-Click*>Clear Contents. Neither of which seem to work.
>
> This is really a problem of aesthetics, it would just be nice to lean
> up the spreadsheet a bit.
>
> Any ideas?
>
> TIA,
>
> SamuelT
>
>
> -- 
> SamuelT
> ------------------------------------------------------------------------
> SamuelT's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=27501
> View this thread: http://www.excelforum.com/showthread.php?threadid=479727
> 


0
me1 (409)
10/27/2005 10:34:13 AM
Hi,

The delete, close and save worked a treat. 

Thanks very much!

Samuel

--
Samuel
-----------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2750
View this thread: http://www.excelforum.com/showthread.php?threadid=47972

0
10/27/2005 2:11:58 PM
Reply:

Similar Artilces:

tranposing every third cell in a row to a column
How do you transpose every third cell in a row of data to a column on a separate worksheet. There should be no empty cells in the column of the separate worksheet that this information is being copied to. Say the data was in Row 1. This will go from A to D to G ... etc. Enter this formula anywhere, and copy down: =INDEX(Sheet1!$1:$1,3*ROWS($1:1)-2) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Wabash" <Wabash@discussions.micro...

shifting of nonblank cells
I'm trying to insert a column on a worksheet. All the columns are unhidden and no cells are merged. When I try to insert the column, I get this error message: To prevent possible losss of data, Microsoft Excel cannot shift nonblank cells off the worksheet. To to delete or clear cells to the right or below the data...... I have been able to insert columns before on this worksheet. I have no idea why this message is coming up now. Please help. Hi, As I recall, this message is telling you that at one (or both) ends (horizontally or vertically) of the spreadsheet, there's data ...

Date Formats for Cells Do not match System Date Formats
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just loaded Office 2008 and then update to the latest update 12.2.4 <br> I have my system preferences set to Australia for date and time using the date format dd/mm/yyyy. But in Excel when I open the format cell dialogue and select date the only formats available for selection are in the mm/dd/yyyy formats. When I checked in &quot;Numbers&quot; the dd/mm/yyyy format is used so &quot;Numbers&quot; is picking up the system preference why not the excel. Did you by any chance have ...

put data in a blank cell if condition met
hi there excellers i have two columns that have data in both or just column a a b row1 0:03:15 0:03:20 row2 0:03:35 row3 0:04:15 0:04:16 what i need is when a cell in column b is empty insert corresponding cell in column a. the cells are time formated. finished project should be like so a b row1 0:03:15 0:03:20 row2 0:03:35 0:03:35 row3 0:04:15 0:04:16 any help much appreciated T.I.A regards Ditchy One way: Select column B. Choose Edit/Goto. Click Special, then select the Blank...

blank cell conditionally formatted
I would like blank cells in my spreadsheet to be white (no shading). I would like the cells with text to be formatted to a selected color. I did a search in this NG but couldn't find any similar situations. Does anyone know how to do this? TIA One way: Format your cells as white (no shading). Select the cells to Conditionally format (with, say, cell A1 the active cell), and set the dropdowns and inputbox to read CF1: Formula is =LEN(A1)>0 Format1: <patterns>/<selected color> In article <1175008431.196607.19430@b75g2000hsg.googlegroups.com>, "da...

Autofit in excel
Everyonce in a while, when I autofit text in excel I get extra space before or after the text. There are no extra spaces in the text and I don't see any formatting issues which would create the extra space. Hi is this a merged cell? -- Regards Frank Kabel Frankfurt, Germany "seezzell" <seezzell@discussions.microsoft.com> schrieb im Newsbeitrag news:8EAE2A6D-4A37-4F23-9503-7A72D868562A@microsoft.com... > Everyonce in a while, when I autofit text in excel I get extra space before > or after the text. There are no extra spaces in the text and I don't see any >...

Group Policy removing CRM Add-on from Outlook
One of our CRM users can no longer access any of the buttons for CRM in Outlook (they can access CRM, but not the "track in CRM" or advanced find etc). Tools > Options > Other > Adv Options > COM Add-Ins does not show CRM, so I had to grant the user local admin rights, so that they could then re-add the COM add-in, which makes everything work. However, as soon as the user logs off and back in, the COM Add-In is gone. What could be possibly be removing this COM add-in? ...

layering logo can't remove white box only lines and text wanted
when trying to place my logo ontop of a background I can't get rid of white box it is in... Only want black lines and text in logo to show with back ground showing through all white areas. Tried following transparent setting info. ... didn't work. Is the logo a bitmap, i.e., .jpg, .tif, bmp, .gif? If it is a .wmf, ungroup it and delete the white box or select no fill. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "sschmitz" <sschmitz@discussions.microsoft.com> wrote in message news:E4E6DA9E-117D-4226-9D0...

HTML tags changing cell shape and size
Hi, I am working on some html text in excel spreadsheet. When I past some HTML in a cell, the cell changes shape according to the html tags For example, text will become bold if <b></b> is used and font change in a similar way. Is there are way I can disable html in a cell altogether where exce treats it just like text and shape of my cell doesn't change? Thank -- wmciu ----------------------------------------------------------------------- wmcius's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1561 View this thread: http://www.excelforum.com/s...

Excel 2007 error msg when saving
In Excel 2007, when saving a file, we get the following message... "Errors were detected while saving FILENAME. Excel may be able to save the file by removing or repairing some features" If we click continue, it gives us the file selection screen to "save as" Press enter, and we get "The file already exists. Do you want to replace the existing file?" If click "yes", the next message is "No errors were detected in FILENAME. No repairs were necessary" Click OK and it saves the file. I have searched newsgroups, but only find references to Tr...

how do I remove spaces from cells that were pasted
I am pasting numbers into a spreadsheet, however its treating them as text because there is a space before the number. How do I remove the space so it treats it as a number ? I have used the =trim() function and its not working. Thanks for any help ! Hi! It sounds like you're copy/pasting from a website. Try this macro from David McRitchie. Look for TRIMALL, it's about half way down the page: http://www.mvps.org/dmcritchie/excel/join.htm Biff >-----Original Message----- >I am pasting numbers into a spreadsheet, however its treating them as text >because there is a...

Sum cells in columns based on condition
Dear all, I would like to multiply and add two columns together using an appropriate function - best if i could avoid anything too complicated like vba :-P The criteria for this is to multiply and add rows in the columns following the A's Col1 ... Col5 A 1 2000 H 2 1000 A 2 2000 So the sum should read 1x2000+2x2000 = 6000 (Col2 to Col4 also contain values as Col1.) I'm not sure how to do this... - I can't put the A's in any other place other than above the numbers, so the sum product doesn't work - I've put in a conditional format to colour the ce...

Formula works in some cells, doesn't in other
I have a multi-sheet 2003 workbook. Sheet 1 is a summary that displays data from the other 4 sheets, the name of one of which is "Northeast" (though experimentation proves that it doesn't matter what the sheet is named) On my summary sheet this particular sheet is referenced in row 4. Column A is nothing but the text "Northeast", the formulas in each of the next columns are as follows (without the B: C: D: E: and F:) B: =SUMIF(Northeast!C:C,">0",Northeast!B:B) C: =SUM(Northeast!C:C) D: =SUM(Northeast!D:D) E: =1-B4/ABS(C4) F: =AVERAGE(IF(Northeast...

Dependent cells
I have a financial model with some 15-20 worksheets. I dont need a lot of these work sheets and would ideally like to delelte them but before that i want to ensure that no cell references to other worksheets i.e. cells in other worksheets are not dependent on the to-be-deleted sheet. I have tried CtrlG>Alt S>dependent cells Formula Auditing tool but these are too tedious when i have too many cells filled in every worksheet. Any other way to ensure that the sheet has no dependents? Tks/Amag Ps: tried http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/9255c6015d...

comma displaying as apostrophe and unwanted extended price roundin
Yesterday (3/19) our GP10 platform began showing the thousands separator as an apostrophe or inverted comma on most documents though the data in SQL was correct. It also began rounding the extended price where a product, quantity 1 at $4.56 was showing as an extended price of $5.00. Since the data in SQL is correct and this is happening on all 20+ clients, we are thinking it may have something to do with the ODBC connection needed on each workstation though no settings have changed and it has been running fine, without this issue, for 18 months. Any ideas? VoIPSupply, Could your curre...

How to get notified when user inserts cells, rows, or columns in W
I'm making a client to Excel that keeps references to cells on worksheets. These references must be updated if the user inserts cells to the left or above the referenced cells. I have not been able to find this information, neither in the Range that accompanies the Change notification nor in any of the Worksheet, Workbook, or Application properties. Can anybody help me? Using the Worksheet_Change event, you could probably evaluate the row and column values of the target cell against the criteria cell: If Target <> (Reference Cell) Then If Target.Row <=( Refere...

Help me ! I want to create two or more functions work in one cell.
I want to create two or more functions to works in one cell, respectively: IF(AND(B5=B21,A5=A22),SUM(C5),"") and IF(AND(B5=B21,A6=A22),SUM(C6),"") Independently they work but when I�m ordering one by one in sell appears FEALS. I tried to apply before OR , IF(OR �. , but something�s wrong. -- Message posted via http://www.officekb.com One way: =(B5=B21)*((A5=A22)*C5 + (A6=A22)*C6) In article <506CF7B1FC9F0@OfficeKB.com>, "peter via OfficeKB.com" <forum@OfficeKB.com> wrote: > I want to create two or more functions to works in one cell...

How to find multiple cells/replace whole cells w/data
I am trying to find a specific set of characters in multiple cells in a column. Once found, I need to replace the data within found cells with a specific text string. I have been able to replace a text string, but not the whole cell. Perform a search and replace, and add an asterisk before and after your "search for" parameter. Replace with your desired cell contents. Search for: *your text here* Replace with: desired cell contents Instead of 'Find', turn on data filters and select Custom... Use the drop-downs to make the condition 'contains' and then ente...

vertical text across multiple cells
How can I type vertical text across multiple cells. I need to type text from C2: downward across several cells depending on the length of the word. If I try it it wants to enlarge the size of C2: to accommodate the text. Highlight the cells across which the text should span. Then on the menu bar: Format > Cells, click the Alignment tab. Select the 'merge cells' checkbox and, if you want, use the dial to orient the text. "BThrasher" wrote: > How can I type vertical text across multiple cells. I need to type text from > C2: downward across several cells depe...

Remove grid lines as default
Hi, I hate to ask again, but I had this all set up with by default - no Grid lines, but after a crash, well I have even lost the email with the info. Would someone please help out again and tell me how to remove grid lines as default, ie no grid lines in new spreadsheets. Many thanks David dcterrill@(REMOVE)bigpond.com You can create a new default template, with your preferred settings: 1. Open a new workbook. 2. Select all the sheets, and choose Tools>Options 3. On the View tab, remove the check mark for Gridlines, click OK 4. Ungroup the sheets 5. Choose Save As 6. From the Save As T...

Locate last cell containing data in a row
I have a spreadsheet whereby each row contains data, but not in al columns. I am interested in a formula, on each row, that would enabl me to read the last cell (to the right) containing data. Would appreciate any suggestion. Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements On Mon, 8 Dec 2003 05:28:40 -0600, Amr Tabbarah <Amr.Tabbarah.y48zy@excelforum-nospam.com> ...

How can I insert numerical bullets in a cell ex: 1a) ?
I am trying to insert several numerical bullets into a cell that contains text. For example, 1a) example 1 1b) example 2 1c) example 3 How can I do this more easily than inserting 3 symbols 1, a, )? You could use a "helper" column with a formula in it. Assuming that your text entries are currently in ColA starting at Row2, try .... =1&Char(Row(A2)+95)&") "&A2 Char(97) = "a", so this will only work for 26 items in your list - it'll go feral after that. The formula could be modified for leading 2,3,4, etc. if you need as well. Rgds, ScottO ...

Checking for black cells
Hi, I have the following formula on my worksheet: =ROUNDUP((C3/D3) + (F3/G3) + (I3/J3) + (L3/M3) + (O3/P3) + (R3/S3) + (U3/V3) + (X3/Y3) + (AA3/AB3) + (AD3/AE3) + (AG3/AH3) + (AJ3/AK3) + (AM3/AN3) + (AP3/AQ3) + (AS3/AT3) + (AV3/AW3) + (AY3/AZ3) + (BB3/BC3), 0) The formula doesn't work if any of the cells are empty, how can I alter it so that it checks for empty cells and only calculates populated cells? Thanks for your advice Well, one way would be: =ROUNDUP(SUM((IF(D3=0,0,C3/D3)+IF(G3=0,0,F3/G3)+IF(J3=0,0,I3/J3)+IF(M3=0,0,L3/M3)+IF(P3=0,0,O3/P3)+IF(S3=0,0,R3/S3)+IF(V3=0,0,U3/V3)),(...

sum using or (if Blank cell in Column)
My Basic problem seems to be that i cannot run a variable against a blank cell. My set up as below: Item 50cl 70cl 1.5cl per 25ml (< answer i'm looking for) Rum £17.50 Cheese £80.35 Milk £10.50 Now individually i can work out these items. Rum would be 0.62 in the 25ml column... but I cannot find how to create each sum in the total box, so when i leave two of them blank it understand to only work out the one answer. It must look something like this? Right? Item 50cl 70cl 1.5cl per 25ml Rum £17.50 =s...

Un-proteced Cell in a Protected Sheet
How can you resize, wrap text and such in an un-protected cell within a protected sheet. Many thanks. SU That would depend upon which version of Excel you are using. 2002 and 2003 allow cell editing on protected sheets. Also formattting of columns and rows for resizing. Earlier versions would require unprotecting, formatting then reprotecting. This could be done via a macro. Gord Dibben Excel MVP On Wed, 20 Apr 2005 09:58:02 -0700, "SU" <SU@discussions.microsoft.com> wrote: >How can you resize, wrap text and such in an un-protected cell within a >protected sh...