PDA

View Full Version : [SOLVED] Troubles with Counting Used Rows



Saladsamurai
09-09-2009, 01:33 PM
Okay then! :)

In the program I am working on, the user enters data into a UserForm and then the Code, does some stuff with the data and then it needs to enter the data onto the next available Row in WorkSheets(1). So I am trying to assign the indes of the Next Available Row to the variable "NextRow"

I tried using


Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
NextRow = Selection.Rows.Count + 1

AND


NextRow = ActiveSheet.UsedRange.Rows.Count + 1


The Problem with both of these is that the functions will Count a row as being used, even if there is no data in it, but if there was at some point.

That is, if I enter data in row 4 and then 'delete' it OR 'clear' it, it will still count that row.

How can I get around this?

Also, if you open a brand new worksheet and run the function


MsgBox ActiveSheet.UsedRange.Rows.Count
or

Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
NextRow = Selection.Rows.Count
MsgBox NextRow

It will always return 1. why? The first Row is empty...

Thanks.

Bob Phillips
09-09-2009, 02:39 PM
If Range("A1").Value = "" Then
NextRow = 1
ElseIf Range("A2").Value = "" Then
NextRow = 2
Else
NextRow = Cells(Rows.Count, "A").End(xlUp).Row
End If

Saladsamurai
09-09-2009, 02:47 PM
If Range("A1").Value = "" Then
NextRow = 1
ElseIf Range("A2").Value = "" Then
NextRow = 2
Else
NextRow = Cells(Rows.Count, "A").End(xlUp).Row
End If



Here is what I like about it: It will not count a cell that previously had data in it, but was deleted.

Here is what I don't like: It only checks column "A".....so technically it is not checking 'rows'.

I might be able to work with this one though. Thanks!