UsedRange - can't figure this out 1

[jm314]

Can anyone figure out why, in sub test(), iRow is always equal to 1 when a blank sheet is being used? (Cells(iRow,1).value = i always puts the data in A1. My intention is that as data is added to a row, getRowCount() should return an incremented number to show a new item has been added. Each datum should have it's own consecutive cell in column A. Code follows:


Public Function getRowCount() As Integer

Dim tempRows As Integer

tempRows = Worksheets("sheet1").UsedRange.Rows.Count

getRowCount = tempRows

End Function


Sub test()

Dim i As Integer, iRow As Integer

For i = 1 To 10
iRow = getRowCount()
Cells(iRow, 1).Value = i
Next

End Sub

 

[byundt]

ActiveSheet.UsedRange.Rows.Count always returns a minimum of 1, even when the sheet is completely empty. Same thing occurs with ActiveSheet.UsedRange.Columns.Count.

To test is a sheet is completely empty, you could count both the rows and columns in the used range, then test cell A1 to see if it is blank.

 

[jm314]

Thanks for the tip! Testing A1 worked:


Public Function getRowCount() As Integer

Dim tempRows As Integer

tempRows = Worksheets("sheet1").UsedRange.Rows.Count
If tempRows = 1 And IsEmpty(Cells(1, 1)) Then
tempRows = 1
Else
If tempRows = 1 And Not (IsEmpty(Cells(1, 1))) Then
tempRows = 2
Else
tempRows = tempRows + 1
End If
End If
getRowCount = tempRows

End Function