Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting All Empty Rows
I am using a macro already recorded, and the only thing I wish I could do
away with is this: When all of the data is brought in and formatted, there are thousands of extra empty rows that exist at the bottom of the document. I want to find a quick way to delete those then automate it, so the macro does it for me. OR Is there a way to sum an entire column automatically within a macro so that the total ends up at the bottom of the columns with values in them. Currently with the thousands of extra empty rows, totalling the column makes the sum appear way at the bottom of the document on line 6,000 something. Thanks!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting All Empty Rows
Try this after editing to get all your rows (replace 1000 by 65000 if you
wish) Sub Macro1() Rows("1:1000").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Range("A1").Select End Sub -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bodhisatvaofboogie" wrote in message ... I am using a macro already recorded, and the only thing I wish I could do away with is this: When all of the data is brought in and formatted, there are thousands of extra empty rows that exist at the bottom of the document. I want to find a quick way to delete those then automate it, so the macro does it for me. OR Is there a way to sum an entire column automatically within a macro so that the total ends up at the bottom of the columns with values in them. Currently with the thousands of extra empty rows, totalling the column makes the sum appear way at the bottom of the document on line 6,000 something. Thanks!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting All Empty Rows
That seems to work great. HOWEVER, doesn't appear to aleviate the
problem...I tried it with 65000 in there, still had countless thousands of lines. Is there a max? OR, is there something else that I'm not noticing? "Bernard Liengme" wrote: Try this after editing to get all your rows (replace 1000 by 65000 if you wish) Sub Macro1() Rows("1:1000").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Range("A1").Select End Sub -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bodhisatvaofboogie" wrote in message ... I am using a macro already recorded, and the only thing I wish I could do away with is this: When all of the data is brought in and formatted, there are thousands of extra empty rows that exist at the bottom of the document. I want to find a quick way to delete those then automate it, so the macro does it for me. OR Is there a way to sum an entire column automatically within a macro so that the total ends up at the bottom of the columns with values in them. Currently with the thousands of extra empty rows, totalling the column makes the sum appear way at the bottom of the document on line 6,000 something. Thanks!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting All Empty Rows
I have a macro in place to delete all empty rows, it works great. NOW, the
problem is this: When I select an entire column, then auto sum it the subtotal is placed on line 65536. Effectively adding back those rows just deleted. So I went to check it out, and after the delete of all blank rows, if I just wheel mouse down the page, when it gets to the bottom of the data sets, the scroll to the right srinks to a sliver and all the rows deleted are added back. What the heck am I missing? *Sigh* "Bernard Liengme" wrote: Try this after editing to get all your rows (replace 1000 by 65000 if you wish) Sub Macro1() Rows("1:1000").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Range("A1").Select End Sub -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bodhisatvaofboogie" wrote in message ... I am using a macro already recorded, and the only thing I wish I could do away with is this: When all of the data is brought in and formatted, there are thousands of extra empty rows that exist at the bottom of the document. I want to find a quick way to delete those then automate it, so the macro does it for me. OR Is there a way to sum an entire column automatically within a macro so that the total ends up at the bottom of the columns with values in them. Currently with the thousands of extra empty rows, totalling the column makes the sum appear way at the bottom of the document on line 6,000 something. Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete empty rows from print in Excel | Excel Worksheet Functions | |||
Deleting All Rows for Duplicate Entries Except Those With Most Items In Row | Excel Discussion (Misc queries) | |||
Deleting and only viewing selected rows | Excel Discussion (Misc queries) | |||
Deleteing empty rows | Excel Discussion (Misc queries) | |||
Deleting rows containing common data | Excel Discussion (Misc queries) |