Deleting 63,886 Blank Rows Under My Data

Discussion in 'Microsoft Excel Misc' started by Kajuliano, Oct 23, 2008.

  1. Kajuliano

    Kajuliano Guest

    I am working on an excel file in which I currently have data in 1,650 rows
    and there are blank cells within the rows I am working on that need to stay
    blank. My main problem is deleteing the extra 65,536 blank rows below my data
    that randomly appeared when I copy and pasted my data to a new workbook. When
    I highlight a single blank row or many blank rows beneath my data and hit
    delete row, they do not go away and it is really annoying because my scroll
    bar had become all but useless- please help! Thanks!
     
    Kajuliano, Oct 23, 2008
    #1
    1. Advertisements

  2. Kajuliano

    FSt1 Guest

    hi
    i think excel has lost it's used range reference.
    see this site to reset.

    http://www.contextures.com/xlfaqApp.html#Unuseda

    regards
    FSt1

    "Kajuliano" wrote:

    > I am working on an excel file in which I currently have data in 1,650 rows
    > and there are blank cells within the rows I am working on that need to stay
    > blank. My main problem is deleteing the extra 65,536 blank rows below my data
    > that randomly appeared when I copy and pasted my data to a new workbook. When
    > I highlight a single blank row or many blank rows beneath my data and hit
    > delete row, they do not go away and it is really annoying because my scroll
    > bar had become all but useless- please help! Thanks!
     
    FSt1, Oct 23, 2008
    #2
    1. Advertisements

  3. Kajuliano

    T. Valko Guest

    See this:

    http://contextures.com/xlfaqApp.html#Unused

    Note that when you "delete" the unused rows you're not literally removing
    them from the the sheet. Technically, what you're actually doing is
    resetting the *used range*.

    --
    Biff
    Microsoft Excel MVP


    "Kajuliano" <> wrote in message
    news:...
    >I am working on an excel file in which I currently have data in 1,650 rows
    > and there are blank cells within the rows I am working on that need to
    > stay
    > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > data
    > that randomly appeared when I copy and pasted my data to a new workbook.
    > When
    > I highlight a single blank row or many blank rows beneath my data and hit
    > delete row, they do not go away and it is really annoying because my
    > scroll
    > bar had become all but useless- please help! Thanks!
     
    T. Valko, Oct 23, 2008
    #3
  4. Kajuliano

    Kajuliano Guest

    Thanks for the website- I tried highlighing the rows and deleting and that
    didn't reset the range. I even saved and closed but I have office 2007 so I
    don't think that is an issue. They give a macro to programatically reset the
    used range but I'm not quite sure how to use the macro-- do I have to put in
    values or can I simply compy and paste it without adding or changing
    anything? Also, every time I open and close this file, this message pops up :
    Compile error in hidden module: DistMon-- think that has anything to do with
    it?? Thanks!!

    "FSt1" wrote:

    > hi
    > i think excel has lost it's used range reference.
    > see this site to reset.
    >
    > http://www.contextures.com/xlfaqApp.html#Unuseda
    >
    > regards
    > FSt1
    >
    > "Kajuliano" wrote:
    >
    > > I am working on an excel file in which I currently have data in 1,650 rows
    > > and there are blank cells within the rows I am working on that need to stay
    > > blank. My main problem is deleteing the extra 65,536 blank rows below my data
    > > that randomly appeared when I copy and pasted my data to a new workbook. When
    > > I highlight a single blank row or many blank rows beneath my data and hit
    > > delete row, they do not go away and it is really annoying because my scroll
    > > bar had become all but useless- please help! Thanks!
     
    Kajuliano, Oct 23, 2008
    #4
  5. Kajuliano

    Kajuliano Guest

    Thanks for the reply-- that was the same website FSt1 directed me to as well
    and I could not reset the used range using this method for some reason. As I
    said in my reply to FSt1 above I am unclear on using the macro to
    programatically reset the used range-- any direction in that area would be
    helpful thanks!

    "T. Valko" wrote:

    > See this:
    >
    > http://contextures.com/xlfaqApp.html#Unused
    >
    > Note that when you "delete" the unused rows you're not literally removing
    > them from the the sheet. Technically, what you're actually doing is
    > resetting the *used range*.
    >
    > --
    > Biff
    > Microsoft Excel MVP
    >
    >
    > "Kajuliano" <> wrote in message
    > news:...
    > >I am working on an excel file in which I currently have data in 1,650 rows
    > > and there are blank cells within the rows I am working on that need to
    > > stay
    > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > data
    > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > When
    > > I highlight a single blank row or many blank rows beneath my data and hit
    > > delete row, they do not go away and it is really annoying because my
    > > scroll
    > > bar had become all but useless- please help! Thanks!

    >
    >
    >
     
    Kajuliano, Oct 23, 2008
    #5
  6. Kajuliano

    T. Valko Guest

    I just tried both methods in an Excel 2007 file and both methods worked.

    I'm kind of suspicious of this:

    >every time I open and close this file, this message pops up
    >: Compile error in hidden module: DistMon--
    >think that has anything to do with it??


    --
    Biff
    Microsoft Excel MVP


    "Kajuliano" <> wrote in message
    news:D...
    > Thanks for the reply-- that was the same website FSt1 directed me to as
    > well
    > and I could not reset the used range using this method for some reason. As
    > I
    > said in my reply to FSt1 above I am unclear on using the macro to
    > programatically reset the used range-- any direction in that area would be
    > helpful thanks!
    >
    > "T. Valko" wrote:
    >
    >> See this:
    >>
    >> http://contextures.com/xlfaqApp.html#Unused
    >>
    >> Note that when you "delete" the unused rows you're not literally removing
    >> them from the the sheet. Technically, what you're actually doing is
    >> resetting the *used range*.
    >>
    >> --
    >> Biff
    >> Microsoft Excel MVP
    >>
    >>
    >> "Kajuliano" <> wrote in message
    >> news:...
    >> >I am working on an excel file in which I currently have data in 1,650
    >> >rows
    >> > and there are blank cells within the rows I am working on that need to
    >> > stay
    >> > blank. My main problem is deleteing the extra 65,536 blank rows below
    >> > my
    >> > data
    >> > that randomly appeared when I copy and pasted my data to a new
    >> > workbook.
    >> > When
    >> > I highlight a single blank row or many blank rows beneath my data and
    >> > hit
    >> > delete row, they do not go away and it is really annoying because my
    >> > scroll
    >> > bar had become all but useless- please help! Thanks!

    >>
    >>
    >>
     
    T. Valko, Oct 23, 2008
    #6
  7. Kajuliano

    Gord Dibben Guest

    For the Distmon error message.

    See KB article
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410

    The PDFMaker.xla and maybe PDFMaker.dot are stored in your Office\XLSTART
    folder or in the Excel\XLSTART folder.

    Any files in these folders will open with Excel.

    You must remove them from the XLSTART folder.


    Gord Dibben MS Excel MVP

    On Wed, 22 Oct 2008 23:45:19 -0400, "T. Valko" <>
    wrote:

    >I just tried both methods in an Excel 2007 file and both methods worked.
    >
    >I'm kind of suspicious of this:
    >
    >>every time I open and close this file, this message pops up
    >>: Compile error in hidden module: DistMon--
    >>think that has anything to do with it??
     
    Gord Dibben, Oct 23, 2008
    #7
  8. Kajuliano

    Kajuliano Guest

    Well i copied and pasted all of my data into a new workbook just to make sure
    I didn't hit some weird setting in the original one and the highlight delete
    method still did not work. Then I copied the macro and ran it and it made it
    so my scroll bar actually works well now, but the extra rows are still there.
    All that I cared about was the scroll bar working so I am satisfied. As for
    the DistMon error I think it has something to do with different Adobe files
    that are stored on my computer (I just looked this up). Thank you for the
    time and the help!!

    "T. Valko" wrote:

    > I just tried both methods in an Excel 2007 file and both methods worked.
    >
    > I'm kind of suspicious of this:
    >
    > >every time I open and close this file, this message pops up
    > >: Compile error in hidden module: DistMon--
    > >think that has anything to do with it??

    >
    > --
    > Biff
    > Microsoft Excel MVP
    >
    >
    > "Kajuliano" <> wrote in message
    > news:D...
    > > Thanks for the reply-- that was the same website FSt1 directed me to as
    > > well
    > > and I could not reset the used range using this method for some reason. As
    > > I
    > > said in my reply to FSt1 above I am unclear on using the macro to
    > > programatically reset the used range-- any direction in that area would be
    > > helpful thanks!
    > >
    > > "T. Valko" wrote:
    > >
    > >> See this:
    > >>
    > >> http://contextures.com/xlfaqApp.html#Unused
    > >>
    > >> Note that when you "delete" the unused rows you're not literally removing
    > >> them from the the sheet. Technically, what you're actually doing is
    > >> resetting the *used range*.
    > >>
    > >> --
    > >> Biff
    > >> Microsoft Excel MVP
    > >>
    > >>
    > >> "Kajuliano" <> wrote in message
    > >> news:...
    > >> >I am working on an excel file in which I currently have data in 1,650
    > >> >rows
    > >> > and there are blank cells within the rows I am working on that need to
    > >> > stay
    > >> > blank. My main problem is deleteing the extra 65,536 blank rows below
    > >> > my
    > >> > data
    > >> > that randomly appeared when I copy and pasted my data to a new
    > >> > workbook.
    > >> > When
    > >> > I highlight a single blank row or many blank rows beneath my data and
    > >> > hit
    > >> > delete row, they do not go away and it is really annoying because my
    > >> > scroll
    > >> > bar had become all but useless- please help! Thanks!
    > >>
    > >>
    > >>

    >
    >
    >
     
    Kajuliano, Oct 23, 2008
    #8
  9. Kajuliano

    Kajuliano Guest

    Thanks-- I am actually working from a remote desktop connection through my
    university and I do not think we have access to change settings but I'm fine
    with this little error message, it doesn't bother me much- thanks though!

    "Gord Dibben" wrote:

    > For the Distmon error message.
    >
    > See KB article
    > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410
    >
    > The PDFMaker.xla and maybe PDFMaker.dot are stored in your Office\XLSTART
    > folder or in the Excel\XLSTART folder.
    >
    > Any files in these folders will open with Excel.
    >
    > You must remove them from the XLSTART folder.
    >
    >
    > Gord Dibben MS Excel MVP
    >
    > On Wed, 22 Oct 2008 23:45:19 -0400, "T. Valko" <>
    > wrote:
    >
    > >I just tried both methods in an Excel 2007 file and both methods worked.
    > >
    > >I'm kind of suspicious of this:
    > >
    > >>every time I open and close this file, this message pops up
    > >>: Compile error in hidden module: DistMon--
    > >>think that has anything to do with it??

    >
    >
     
    Kajuliano, Oct 23, 2008
    #9
  10. Kajuliano

    T. Valko Guest

    Glad to hear you got it to work (one way or the other!).

    >I copied the macro and ran it and it made it so my
    >scroll bar actually works well now, but the extra
    >rows are still there.


    Yeah, as I noted in my other reply you're not actually deleting (removing)
    the rows/columns. You're just resetting where Excel thinks your data ends
    (the used range). The scroll bar operation correlates to the size of the
    used range.

    --
    Biff
    Microsoft Excel MVP


    "Kajuliano" <> wrote in message
    news:...
    > Well i copied and pasted all of my data into a new workbook just to make
    > sure
    > I didn't hit some weird setting in the original one and the highlight
    > delete
    > method still did not work. Then I copied the macro and ran it and it made
    > it
    > so my scroll bar actually works well now, but the extra rows are still
    > there.
    > All that I cared about was the scroll bar working so I am satisfied. As
    > for
    > the DistMon error I think it has something to do with different Adobe
    > files
    > that are stored on my computer (I just looked this up). Thank you for the
    > time and the help!!
    >
    > "T. Valko" wrote:
    >
    >> I just tried both methods in an Excel 2007 file and both methods worked.
    >>
    >> I'm kind of suspicious of this:
    >>
    >> >every time I open and close this file, this message pops up
    >> >: Compile error in hidden module: DistMon--
    >> >think that has anything to do with it??

    >>
    >> --
    >> Biff
    >> Microsoft Excel MVP
    >>
    >>
    >> "Kajuliano" <> wrote in message
    >> news:D...
    >> > Thanks for the reply-- that was the same website FSt1 directed me to as
    >> > well
    >> > and I could not reset the used range using this method for some reason.
    >> > As
    >> > I
    >> > said in my reply to FSt1 above I am unclear on using the macro to
    >> > programatically reset the used range-- any direction in that area would
    >> > be
    >> > helpful thanks!
    >> >
    >> > "T. Valko" wrote:
    >> >
    >> >> See this:
    >> >>
    >> >> http://contextures.com/xlfaqApp.html#Unused
    >> >>
    >> >> Note that when you "delete" the unused rows you're not literally
    >> >> removing
    >> >> them from the the sheet. Technically, what you're actually doing is
    >> >> resetting the *used range*.
    >> >>
    >> >> --
    >> >> Biff
    >> >> Microsoft Excel MVP
    >> >>
    >> >>
    >> >> "Kajuliano" <> wrote in message
    >> >> news:...
    >> >> >I am working on an excel file in which I currently have data in 1,650
    >> >> >rows
    >> >> > and there are blank cells within the rows I am working on that need
    >> >> > to
    >> >> > stay
    >> >> > blank. My main problem is deleteing the extra 65,536 blank rows
    >> >> > below
    >> >> > my
    >> >> > data
    >> >> > that randomly appeared when I copy and pasted my data to a new
    >> >> > workbook.
    >> >> > When
    >> >> > I highlight a single blank row or many blank rows beneath my data
    >> >> > and
    >> >> > hit
    >> >> > delete row, they do not go away and it is really annoying because my
    >> >> > scroll
    >> >> > bar had become all but useless- please help! Thanks!
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
    T. Valko, Oct 23, 2008
    #10
  11. Kajuliano

    rory_r Guest

    Thanks for this info.I used it too.
    However I keep getting an error when I run the same script from the link.
    the debugger stops here- with an application-defined or object defined errror.

    Else
    .Range(.Cells(myLastRow + 1, 1), _
    .Cells(.Rows.Count, 1)).EntireRow.Delete
    .Range(.Cells(1, myLastCol + 1), _
    .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
    End With

    Any ideas
    Rory

    "T. Valko" wrote:

    > See this:
    >
    > http://contextures.com/xlfaqApp.html#Unused
    >
    > Note that when you "delete" the unused rows you're not literally removing
    > them from the the sheet. Technically, what you're actually doing is
    > resetting the *used range*.
    >
    > --
    > Biff
    > Microsoft Excel MVP
    >
    >
    > "Kajuliano" <> wrote in message
    > news:...
    > >I am working on an excel file in which I currently have data in 1,650 rows
    > > and there are blank cells within the rows I am working on that need to
    > > stay
    > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > data
    > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > When
    > > I highlight a single blank row or many blank rows beneath my data and hit
    > > delete row, they do not go away and it is really annoying because my
    > > scroll
    > > bar had become all but useless- please help! Thanks!

    >
    >
    >
     
    rory_r, Dec 24, 2008
    #11
  12. I'm guessing that it's the first logical line (first 2 lines of code) that is
    causing the trouble.

    What does myLastRow equal?

    Add
    MsgBox MyLastRow
    right before this portion of code.

    And is there any chance that the worksheet is protected?

    rory_r wrote:
    >
    > Thanks for this info.I used it too.
    > However I keep getting an error when I run the same script from the link.
    > the debugger stops here- with an application-defined or object defined errror.
    >
    > Else
    > .Range(.Cells(myLastRow + 1, 1), _
    > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > .Range(.Cells(1, myLastCol + 1), _
    > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > End If
    > End With
    >
    > Any ideas
    > Rory
    >
    > "T. Valko" wrote:
    >
    > > See this:
    > >
    > > http://contextures.com/xlfaqApp.html#Unused
    > >
    > > Note that when you "delete" the unused rows you're not literally removing
    > > them from the the sheet. Technically, what you're actually doing is
    > > resetting the *used range*.
    > >
    > > --
    > > Biff
    > > Microsoft Excel MVP
    > >
    > >
    > > "Kajuliano" <> wrote in message
    > > news:...
    > > >I am working on an excel file in which I currently have data in 1,650 rows
    > > > and there are blank cells within the rows I am working on that need to
    > > > stay
    > > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > > data
    > > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > > When
    > > > I highlight a single blank row or many blank rows beneath my data and hit
    > > > delete row, they do not go away and it is really annoying because my
    > > > scroll
    > > > bar had become all but useless- please help! Thanks!

    > >
    > >
    > >


    --

    Dave Peterson
     
    Dave Peterson, Dec 24, 2008
    #12
  13. Kajuliano

    rory_r Guest

    I ran the MSG box and it returned 0
    After I run the script and get the error (with the option of running the
    debugger), the spread sheets extra rows over 600 are gone. So it seems to
    work to that point.

    As far as protected, it's not, it probably should be tho.
    Rory

    "Dave Peterson" wrote:

    > I'm guessing that it's the first logical line (first 2 lines of code) that is
    > causing the trouble.
    >
    > What does myLastRow equal?
    >
    > Add
    > MsgBox MyLastRow
    > right before this portion of code.
    >
    > And is there any chance that the worksheet is protected?
    >
    > rory_r wrote:
    > >
    > > Thanks for this info.I used it too.
    > > However I keep getting an error when I run the same script from the link.
    > > the debugger stops here- with an application-defined or object defined errror.
    > >
    > > Else
    > > .Range(.Cells(myLastRow + 1, 1), _
    > > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > > .Range(.Cells(1, myLastCol + 1), _
    > > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > > End If
    > > End With
    > >
    > > Any ideas
    > > Rory
    > >
    > > "T. Valko" wrote:
    > >
    > > > See this:
    > > >
    > > > http://contextures.com/xlfaqApp.html#Unused
    > > >
    > > > Note that when you "delete" the unused rows you're not literally removing
    > > > them from the the sheet. Technically, what you're actually doing is
    > > > resetting the *used range*.
    > > >
    > > > --
    > > > Biff
    > > > Microsoft Excel MVP
    > > >
    > > >
    > > > "Kajuliano" <> wrote in message
    > > > news:...
    > > > >I am working on an excel file in which I currently have data in 1,650 rows
    > > > > and there are blank cells within the rows I am working on that need to
    > > > > stay
    > > > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > > > data
    > > > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > > > When
    > > > > I highlight a single blank row or many blank rows beneath my data and hit
    > > > > delete row, they do not go away and it is really annoying because my
    > > > > scroll
    > > > > bar had become all but useless- please help! Thanks!
    > > >
    > > >
    > > >

    >
    > --
    >
    > Dave Peterson
    >
     
    rory_r, Dec 31, 2008
    #13
  14. One of the things that isn't apparent to me is what the .range() and .cells()
    refer to.

    There's a "with ..." statement that you haven't shared. Maybe it's not what you
    think.

    If you're using a variable in that with statement, share what it holds, too.

    rory_r wrote:
    >
    > I ran the MSG box and it returned 0
    > After I run the script and get the error (with the option of running the
    > debugger), the spread sheets extra rows over 600 are gone. So it seems to
    > work to that point.
    >
    > As far as protected, it's not, it probably should be tho.
    > Rory
    >
    > "Dave Peterson" wrote:
    >
    > > I'm guessing that it's the first logical line (first 2 lines of code) that is
    > > causing the trouble.
    > >
    > > What does myLastRow equal?
    > >
    > > Add
    > > MsgBox MyLastRow
    > > right before this portion of code.
    > >
    > > And is there any chance that the worksheet is protected?
    > >
    > > rory_r wrote:
    > > >
    > > > Thanks for this info.I used it too.
    > > > However I keep getting an error when I run the same script from the link.
    > > > the debugger stops here- with an application-defined or object defined errror.
    > > >
    > > > Else
    > > > .Range(.Cells(myLastRow + 1, 1), _
    > > > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > > > .Range(.Cells(1, myLastCol + 1), _
    > > > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > > > End If
    > > > End With
    > > >
    > > > Any ideas
    > > > Rory
    > > >
    > > > "T. Valko" wrote:
    > > >
    > > > > See this:
    > > > >
    > > > > http://contextures.com/xlfaqApp.html#Unused
    > > > >
    > > > > Note that when you "delete" the unused rows you're not literally removing
    > > > > them from the the sheet. Technically, what you're actually doing is
    > > > > resetting the *used range*.
    > > > >
    > > > > --
    > > > > Biff
    > > > > Microsoft Excel MVP
    > > > >
    > > > >
    > > > > "Kajuliano" <> wrote in message
    > > > > news:...
    > > > > >I am working on an excel file in which I currently have data in 1,650 rows
    > > > > > and there are blank cells within the rows I am working on that need to
    > > > > > stay
    > > > > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > > > > data
    > > > > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > > > > When
    > > > > > I highlight a single blank row or many blank rows beneath my data and hit
    > > > > > delete row, they do not go away and it is really annoying because my
    > > > > > scroll
    > > > > > bar had become all but useless- please help! Thanks!
    > > > >
    > > > >
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson
     
    Dave Peterson, Dec 31, 2008
    #14
  15. Kajuliano

    rory_r Guest

    Dave,
    FIrstly, thanks so much for your help.
    Here's the entire section.

    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim wks As Worksheet
    Dim dummyRng As Range


    For Each wks In ActiveWorkbook.Worksheets
    With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
    .Cells.Find("*", after:=.Cells(1), _
    LookIn:=xlFormulas, lookat:=xlWhole, _
    searchdirection:=xlPrevious, _
    searchorder:=xlByRows).Row
    myLastCol = _
    .Cells.Find("*", after:=.Cells(1), _
    LookIn:=xlFormulas, lookat:=xlWhole, _
    searchdirection:=xlPrevious, _
    searchorder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
    .Columns.Delete
    Else
    .Range(.Cells(myLastRow + 1, 1), _
    .Cells(.Rows.Count, 1)).EntireRow.Delete
    .Range(.Cells(1, myLastCol + 1), _
    .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
    End With
    Next wks

    End Sub
    "Dave Peterson" wrote:

    > One of the things that isn't apparent to me is what the .range() and .cells()
    > refer to.
    >
    > There's a "with ..." statement that you haven't shared. Maybe it's not what you
    > think.
    >
    > If you're using a variable in that with statement, share what it holds, too.
    >
    > rory_r wrote:
    > >
    > > I ran the MSG box and it returned 0
    > > After I run the script and get the error (with the option of running the
    > > debugger), the spread sheets extra rows over 600 are gone. So it seems to
    > > work to that point.
    > >
    > > As far as protected, it's not, it probably should be tho.
    > > Rory
    > >
    > > "Dave Peterson" wrote:
    > >
    > > > I'm guessing that it's the first logical line (first 2 lines of code) that is
    > > > causing the trouble.
    > > >
    > > > What does myLastRow equal?
    > > >
    > > > Add
    > > > MsgBox MyLastRow
    > > > right before this portion of code.
    > > >
    > > > And is there any chance that the worksheet is protected?
    > > >
    > > > rory_r wrote:
    > > > >
    > > > > Thanks for this info.I used it too.
    > > > > However I keep getting an error when I run the same script from the link.
    > > > > the debugger stops here- with an application-defined or object defined errror.
    > > > >
    > > > > Else
    > > > > .Range(.Cells(myLastRow + 1, 1), _
    > > > > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > > > > .Range(.Cells(1, myLastCol + 1), _
    > > > > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > > > > End If
    > > > > End With
    > > > >
    > > > > Any ideas
    > > > > Rory
    > > > >
    > > > > "T. Valko" wrote:
    > > > >
    > > > > > See this:
    > > > > >
    > > > > > http://contextures.com/xlfaqApp.html#Unused
    > > > > >
    > > > > > Note that when you "delete" the unused rows you're not literally removing
    > > > > > them from the the sheet. Technically, what you're actually doing is
    > > > > > resetting the *used range*.
    > > > > >
    > > > > > --
    > > > > > Biff
    > > > > > Microsoft Excel MVP
    > > > > >
    > > > > >
    > > > > > "Kajuliano" <> wrote in message
    > > > > > news:...
    > > > > > >I am working on an excel file in which I currently have data in 1,650 rows
    > > > > > > and there are blank cells within the rows I am working on that need to
    > > > > > > stay
    > > > > > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > > > > > data
    > > > > > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > > > > > When
    > > > > > > I highlight a single blank row or many blank rows beneath my data and hit
    > > > > > > delete row, they do not go away and it is really annoying because my
    > > > > > > scroll
    > > > > > > bar had become all but useless- please help! Thanks!
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >

    >
    > --
    >
    > Dave Peterson
    >
     
    rory_r, Jan 19, 2009
    #15
  16. I don't understand how it blows up on the Else portion of this section:

    > If myLastRow * myLastCol = 0 Then
    > .Columns.Delete
    > Else
    > .Range(.Cells(myLastRow + 1, 1), _
    > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > .Range(.Cells(1, myLastCol + 1), _
    > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > End If


    If myLastRow is really 0, then the "then" portion should be followed. That
    means this line:

    ..columns.delete

    should be the line that's running.

    I don't have a guess.

    rory_r wrote:
    >
    > Dave,
    > FIrstly, thanks so much for your help.
    > Here's the entire section.
    >
    > Dim myLastRow As Long
    > Dim myLastCol As Long
    > Dim wks As Worksheet
    > Dim dummyRng As Range
    >
    > For Each wks In ActiveWorkbook.Worksheets
    > With wks
    > myLastRow = 0
    > myLastCol = 0
    > Set dummyRng = .UsedRange
    > On Error Resume Next
    > myLastRow = _
    > .Cells.Find("*", after:=.Cells(1), _
    > LookIn:=xlFormulas, lookat:=xlWhole, _
    > searchdirection:=xlPrevious, _
    > searchorder:=xlByRows).Row
    > myLastCol = _
    > .Cells.Find("*", after:=.Cells(1), _
    > LookIn:=xlFormulas, lookat:=xlWhole, _
    > searchdirection:=xlPrevious, _
    > searchorder:=xlByColumns).Column
    > On Error GoTo 0
    >
    > If myLastRow * myLastCol = 0 Then
    > .Columns.Delete
    > Else
    > .Range(.Cells(myLastRow + 1, 1), _
    > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > .Range(.Cells(1, myLastCol + 1), _
    > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > End If
    > End With
    > Next wks
    >
    > End Sub
    > "Dave Peterson" wrote:
    >
    > > One of the things that isn't apparent to me is what the .range() and .cells()
    > > refer to.
    > >
    > > There's a "with ..." statement that you haven't shared. Maybe it's not what you
    > > think.
    > >
    > > If you're using a variable in that with statement, share what it holds, too.
    > >
    > > rory_r wrote:
    > > >
    > > > I ran the MSG box and it returned 0
    > > > After I run the script and get the error (with the option of running the
    > > > debugger), the spread sheets extra rows over 600 are gone. So it seems to
    > > > work to that point.
    > > >
    > > > As far as protected, it's not, it probably should be tho.
    > > > Rory
    > > >
    > > > "Dave Peterson" wrote:
    > > >
    > > > > I'm guessing that it's the first logical line (first 2 lines of code) that is
    > > > > causing the trouble.
    > > > >
    > > > > What does myLastRow equal?
    > > > >
    > > > > Add
    > > > > MsgBox MyLastRow
    > > > > right before this portion of code.
    > > > >
    > > > > And is there any chance that the worksheet is protected?
    > > > >
    > > > > rory_r wrote:
    > > > > >
    > > > > > Thanks for this info.I used it too.
    > > > > > However I keep getting an error when I run the same script from the link.
    > > > > > the debugger stops here- with an application-defined or object defined errror.
    > > > > >
    > > > > > Else
    > > > > > .Range(.Cells(myLastRow + 1, 1), _
    > > > > > .Cells(.Rows.Count, 1)).EntireRow.Delete
    > > > > > .Range(.Cells(1, myLastCol + 1), _
    > > > > > .Cells(1, .Columns.Count)).EntireColumn.Delete
    > > > > > End If
    > > > > > End With
    > > > > >
    > > > > > Any ideas
    > > > > > Rory
    > > > > >
    > > > > > "T. Valko" wrote:
    > > > > >
    > > > > > > See this:
    > > > > > >
    > > > > > > http://contextures.com/xlfaqApp.html#Unused
    > > > > > >
    > > > > > > Note that when you "delete" the unused rows you're not literally removing
    > > > > > > them from the the sheet. Technically, what you're actually doing is
    > > > > > > resetting the *used range*.
    > > > > > >
    > > > > > > --
    > > > > > > Biff
    > > > > > > Microsoft Excel MVP
    > > > > > >
    > > > > > >
    > > > > > > "Kajuliano" <> wrote in message
    > > > > > > news:...
    > > > > > > >I am working on an excel file in which I currently have data in 1,650 rows
    > > > > > > > and there are blank cells within the rows I am working on that need to
    > > > > > > > stay
    > > > > > > > blank. My main problem is deleteing the extra 65,536 blank rows below my
    > > > > > > > data
    > > > > > > > that randomly appeared when I copy and pasted my data to a new workbook.
    > > > > > > > When
    > > > > > > > I highlight a single blank row or many blank rows beneath my data and hit
    > > > > > > > delete row, they do not go away and it is really annoying because my
    > > > > > > > scroll
    > > > > > > > bar had become all but useless- please help! Thanks!
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson
     
    Dave Peterson, Jan 19, 2009
    #16
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.