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

[Legacy 169354]

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

 

[pbornemeier]

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:

LastPopulatedCellInColumnA = Columns(1).Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False).Row

 

[Legacy 169354]

Thanks!,

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

 

[Rasm]

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

 

[Legacy 169354]

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</pre>

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

 

[Legacy 169354]

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

 

[xenou]

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.

 

[Legacy 169354]

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

 

[xenou]

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

 

[Legacy 169354]

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

More **** than class I assure you