Page 1 of 2 12 LastLast
Results 1 to 10 of 14

End Up vs. CurrentRegion.Rows Count

This is a discussion on End Up vs. CurrentRegion.Rows Count within the Excel Questions forums, part of the Question Forums category; What is everyone's opinion on the following. Often, I have to loop through large amounts of data inserting, say, formulas ...

  1. #1
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default End Up vs. CurrentRegion.Rows Count

    What is everyone's opinion on the following. Often, I have to loop through large amounts of data inserting, say, formulas to the last row. I'm trying to figure what is overall best to use. I've been using something such as the following:

    MyRegion=Sheet1.Cells.CurrentRegion.Rows
    MyRows=MyRegion.Count

    But, now I'm thinking for numerous reasons that this isn't so good. It might not select the entire range due to blank rows or columns, I think its kind of slow, etc.

    Would it be better to use:

    For Each ThisCell2 In ActiveSheet.Range("F1:F" & Range("F65536").End(xlUp).Row) (courtesy of phantom1975)

    Just wondering.
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Well, if you're going there, you should try to avoid loops too !

    And why not calculate MyRegion as

    MyRegion = Sheet1.UsedRange.Rows.Count
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default

    I never heard of UsedRange. I looked it up in VBA help. Doesn't say much but I'm assuming it selects ALL data in the sheet whether there's blank rows or columns. If this is incorrect please correct me. If this IS correct, how wonderful!

    As for avoiding loops, actually I usually do by something like:

    MyData=Sheet1.Cells.CurrentRange.Rows
    MyRange=MyData.Count
    Range("A1:A" & MyRange)...

    Is this ok or can it be improved?
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi tbardoni:

    How about simplifying it a bit to ...

    Range("F1:F"&[F65536].end(xlup).row)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default

    UsedRange and End(xlUp) do not necessarily yield the same result. A simple test. Enter something in E10; doesn't matter what, anything will do. Now, check the you get from Usedrange and range("F...").End(xlUp).

    Also, if you want to use the End(xlUp) option, use Cells(rows.count,Columns("F").column).end(xlup)

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Quote Originally Posted by tbardoni
    I never heard of UsedRange. I looked it up in VBA help. Doesn't say much but I'm assuming it selects ALL data in the sheet whether there's blank rows or columns. If this is incorrect please correct me. If this IS correct, how wonderful!

    As for avoiding loops, actually I usually do by something like:

    MyData=Sheet1.Cells.CurrentRange.Rows
    MyRange=MyData.Count
    Range("A1:A" & MyRange)...

    Is this ok or can it be improved?
    How about this ?

    Intersect(Range("A:A"), ActiveSheet.UsedRange)

    Also, where did you find CurrentRange ? do you mean CurrentRegion ? and do you want to avoid the possible blanks ? or do you want the whole area of the sheet to be "counted" ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default

    Thanks Tusharm. I see what you're saying.

    Yogi Anand,
    That's what my original post asks. Is it better to use your suggestion? I guess I'm worried that all of my data won't be selected in using CurrentRegion. So, I'm looking for a "safer" solution.

    I'm just trying to get some opinions on this matter.

    Thanks to all!

    Edit: Juan, I meant CurrentRegion.
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Another method for going thru a collection of cells is to use the Special Cells funciton. It only takes you to the cells that meet the specified criteria.

    ' Only go to cells that have numbers in them in Col A
    For Each c In Range("A:A").SpecialCells(xlCellTypeConstants, 1)

    Next c

    'ONly go to cells that have number in the entire sheet
    For Each c In Cells.SpecialCells(xlCellTypeConstants, 1)

    Next c

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Quote Originally Posted by tbardoni
    Thanks Tusharm. I see what you're saying.

    Yogi Anand,
    That's what my original post asks. Is it better to use your suggestion? I guess I'm worried that all of my data won't be selected in using CurrentRegion. So, I'm looking for a "safer" solution.

    I'm just trying to get some opinions on this matter.

    Thanks to all!

    Edit: Juan, I meant CurrentRegion.
    If you are trying to pickup the entire range (considering column F) including any potential blanks, then going up from cell F65536 to the first occupied cell that is encountered is indeed foolproof.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default

    Thanks, Yogi. I've been using CurrentRegion, but then it occurred to me that this could be a potential problem. I thank you again for your suggestion.
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com