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

L

Legacy 169354

Guest
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 a moderator:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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 :)
 
Upvote 0
I think its actually counting the "" (null) in the 'If' formula! anyway around this.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
So you've got some tricks up your sleeve! Sounds like a winner to me.
ξ
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top