Results 1 to 10 of 10

RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

This is a discussion on RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536 within the Excel Questions forums, part of the Question Forums category; Hi, I have a standard vba script that adds a form data to rows and then increments to the next ...

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Posts
    59

    Exclamation RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    Hi,

    I have a standard vba script that adds a form data to rows and then increments to the next row to add further data.

    this script works fine on a new sheet i created, but if i change the sheet to one already made then the rowcount gets tricked into thinking the cells are full up to excels limit(65536)! This sheet I need it in contains merged cells (which need to be there) but as I am using "b1" it is counting only single cells so it should be ok.

    My question is: What can trick the rowcount into thinking there is data in cells than there is really is? !!

    'write data to worksheet AND check previous row for duplicate entry
    Dim RowCount As Long
    RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count

    With Worksheets("Data").Range("b1")

    .Offset(RowCount, 0).Value = Me.txt_refnum.Value

    .Offset(RowCount, 1).Value = DateValue(Me.txt_date.Value)

    .Offset(RowCount, 2).Value = Me.txt_time.Value

    .Offset(RowCount, 3).Value = Me.txt_port.Value

    .Offset(RowCount, 4).Value = Me.Combo_phase_flt.Value

    .Offset(RowCount, 5).Value = Me.Combo_species.Value

    .Offset(RowCount, 6).Value = Me.Combo_effect.Value

    End With
    Last edited by barbs666; Mar 24th, 2011 at 08:57 PM.

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    2,408

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    As far as I know CurrentRegion.Rows.Count cannot be "tricked".

    Put this formula in a cell not in column A on the worksheet and see how many cells it counts as having something in it:

    =COUNTA(A:A)


    This VBA code will always show the last populated row in column A:
    Code:
    LastPopulatedCellInColumnA = Columns(1).Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row
    Phil

    - Display worksheets using Excel Jeanie or HTML Maker
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes (use CODE to keep your code formatted)

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Posts
    59

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    Thanks!,

    will try it when I get to work on monday and let you know!

  4. #4
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    496

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    PS when you move to xl2007 - the limit is then over 1 Mill rows - hehehe
    xl2007 - Windows-7 & XP

    Does xl hate the number 255 ?
    biggest limitation - drives me insane

  5. #5
    Board Regular
    Join Date
    Jan 2011
    Posts
    59

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    Hi pbornemeier,

    I tried the code
    LastPopulatedCellInColumnA = Columns(1).Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False).Row
    maybe I am implementing it wrong, but it will not enter the form into a cell with a formula in it and no visible content. Should I be replacing range with 'LastPopulatedCellInColumnA'??

    thanks in advance

  6. #6
    Board Regular
    Join Date
    Jan 2011
    Posts
    59

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    I think its actually counting the "" (null) in the 'If' formula! anyway around this.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,066

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    You have formulas filling the entire column? It may be needed, but on the other hand, I've personally never filled a worksheet with formulas top to bottom in any files other than experiments.

    If you keep all the formulas then they are part of the current region - no trick there. One workaround might be to use a formula that counts cells excluding the "" values - then use this formula to get the "last row" (it could even be hidden in a defined name). A Count() formula on a row that gets numeric data when it's populated would work - you'd be counting the cells with values, and ignoring the cells with empty strings.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    Board Regular
    Join Date
    Jan 2011
    Posts
    59

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    Hi I got around it by using the formula below:
    this counted in a particular column I referenced by the number 2. That way I could clear any formula in the cells and it counted corectly to the next blank row!


    VARIABLE_NAME = Worksheets("the_sheet_name").Cells(Rows.Count, 2).End(xlUp).Row

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,066

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    So you've got some tricks up your sleeve! Sounds like a winner to me.
    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Jan 2011
    Posts
    59

    Default Re: RowCount = Worksheets("Data").Range("b1").CurrentRegion.Rows.Count being tricked into thinking its 65536

    Quote Originally Posted by xenou View Post
    So you've got some tricks up your sleeve! Sounds like a winner to me.
    ξ

    More **** than class I assure you

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