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

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" <[email protected]> wrote in
message news:[email protected]...
>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!!!

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" <[email protected]> wrote in
> message news:[email protected]...
> >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!!!
>
>
>

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" <[email protected]> wrote in
> message news:[email protected]...
> >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!!!
>
>
>