+ Reply to Thread
Results 1 to 4 of 4

Deleting All Empty Rows

  1. #1
    bodhisatvaofboogie
    Guest

    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. #2
    Bernard Liengme
    Guest

    Re: 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" <bodhisatvaofboogie@discussions.microsoft.com> wrote in
    message news:B920E5E5-9D48-43CA-B15E-04DF4EE178C6@microsoft.com...
    >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. #3
    bodhisatvaofboogie
    Guest

    Re: 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" <bodhisatvaofboogie@discussions.microsoft.com> wrote in
    > message news:B920E5E5-9D48-43CA-B15E-04DF4EE178C6@microsoft.com...
    > >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. #4
    bodhisatvaofboogie
    Guest

    Re: 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" <bodhisatvaofboogie@discussions.microsoft.com> wrote in
    > message news:B920E5E5-9D48-43CA-B15E-04DF4EE178C6@microsoft.com...
    > >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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1