+ Reply to Thread
Results 1 to 5 of 5

UsedRange higher than expected

  1. #1
    ChrisO
    Guest

    UsedRange higher than expected

    Hello,

    I have a sheet where I only have cells populated to row 150 and column 12
    (L) but:

    maxrow = Worksheets(sheetname).UsedRange.Rows.Count
    maxcol = Worksheets(sheetname).UsedRange.Columns.Count

    reports 5006 and 19 respectively. I have even tried using .Clear,
    ..Borders.LineStyle = xlNone and .Interior.ColorIndex = xlColorIndexNone for
    rows 151 onwards, but UsedRange still reports the same figures.

    Can anyone tell me why, and how I can clear rows 151 onwards? The file is
    bigger than I'd expect so I can believe there is something in these rows but
    I cannot see what it is or how to get rid of it.

    Thanks in advance, Chris


  2. #2
    Niek Otten
    Guest

    Re: UsedRange higher than expected

    Hi Chris,

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

    --
    Kind regards,

    Niek Otten

    "ChrisO" <[email protected]> wrote in message news:[email protected]...
    > Hello,
    >
    > I have a sheet where I only have cells populated to row 150 and column 12
    > (L) but:
    >
    > maxrow = Worksheets(sheetname).UsedRange.Rows.Count
    > maxcol = Worksheets(sheetname).UsedRange.Columns.Count
    >
    > reports 5006 and 19 respectively. I have even tried using .Clear,
    > .Borders.LineStyle = xlNone and .Interior.ColorIndex = xlColorIndexNone for
    > rows 151 onwards, but UsedRange still reports the same figures.
    >
    > Can anyone tell me why, and how I can clear rows 151 onwards? The file is
    > bigger than I'd expect so I can believe there is something in these rows but
    > I cannot see what it is or how to get rid of it.
    >
    > Thanks in advance, Chris
    >




  3. #3
    Tom Ogilvy
    Guest

    RE: UsedRange higher than expected

    See Debra Dalgleish's FAQ entry on this:

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

    --
    Regards,
    Tom Ogilvy



    "ChrisO" wrote:

    > Hello,
    >
    > I have a sheet where I only have cells populated to row 150 and column 12
    > (L) but:
    >
    > maxrow = Worksheets(sheetname).UsedRange.Rows.Count
    > maxcol = Worksheets(sheetname).UsedRange.Columns.Count
    >
    > reports 5006 and 19 respectively. I have even tried using .Clear,
    > .Borders.LineStyle = xlNone and .Interior.ColorIndex = xlColorIndexNone for
    > rows 151 onwards, but UsedRange still reports the same figures.
    >
    > Can anyone tell me why, and how I can clear rows 151 onwards? The file is
    > bigger than I'd expect so I can believe there is something in these rows but
    > I cannot see what it is or how to get rid of it.
    >
    > Thanks in advance, Chris
    >


  4. #4
    ChrisO
    Guest

    Re: UsedRange higher than expected

    Niek,

    Many thanks. EntireRow.Delete and EntireColumn.Delete for the erroneous rows
    and columns did the trick.

    Chris

    "Niek Otten" wrote:

    > Hi Chris,
    >
    > http://www.contextures.com/xlfaqApp.html#Unused
    >
    > --
    > Kind regards,
    >
    > Niek Otten
    >
    > "ChrisO" <[email protected]> wrote in message news:[email protected]...
    > > Hello,
    > >
    > > I have a sheet where I only have cells populated to row 150 and column 12
    > > (L) but:
    > >
    > > maxrow = Worksheets(sheetname).UsedRange.Rows.Count
    > > maxcol = Worksheets(sheetname).UsedRange.Columns.Count
    > >
    > > reports 5006 and 19 respectively. I have even tried using .Clear,
    > > .Borders.LineStyle = xlNone and .Interior.ColorIndex = xlColorIndexNone for
    > > rows 151 onwards, but UsedRange still reports the same figures.
    > >
    > > Can anyone tell me why, and how I can clear rows 151 onwards? The file is
    > > bigger than I'd expect so I can believe there is something in these rows but
    > > I cannot see what it is or how to get rid of it.
    > >
    > > Thanks in advance, Chris
    > >

    >
    >
    >


  5. #5
    ChrisO
    Guest

    RE: UsedRange higher than expected

    Thanks Tom, this did the trick.

    Chris

    "Tom Ogilvy" wrote:

    > See Debra Dalgleish's FAQ entry on this:
    >
    > http://www.contextures.com/xlfaqApp.html#Unused
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    > "ChrisO" wrote:
    >
    > > Hello,
    > >
    > > I have a sheet where I only have cells populated to row 150 and column 12
    > > (L) but:
    > >
    > > maxrow = Worksheets(sheetname).UsedRange.Rows.Count
    > > maxcol = Worksheets(sheetname).UsedRange.Columns.Count
    > >
    > > reports 5006 and 19 respectively. I have even tried using .Clear,
    > > .Borders.LineStyle = xlNone and .Interior.ColorIndex = xlColorIndexNone for
    > > rows 151 onwards, but UsedRange still reports the same figures.
    > >
    > > Can anyone tell me why, and how I can clear rows 151 onwards? The file is
    > > bigger than I'd expect so I can believe there is something in these rows but
    > > I cannot see what it is or how to get rid of it.
    > >
    > > Thanks in advance, Chris
    > >


+ 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