Getting new empty row ?

[kleysonr]

How can i get the number of the first empty row in a column ?

 

[Bob Phillips]

Range("A1").End(xlDown).Offset(1,0).Address

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

This function should do that for you...

Function FirstEmptyRow(Col As Variant) As Long
Dim Blanks As Range
If Cells(1, Col).Formula = "" Then
FirstEmptyRow = 1
Else
On Error GoTo NoInternalBlanks
FirstEmptyRow = Columns(Col).SpecialCells(xlCellTypeBlanks)(1).Row
End If
Exit Function
NoInternalBlanks:
FirstEmptyRow = Cells(Rows.Count, Col).End(xlUp).Row + 1
End Function

Just call it from your own code and pass it the column number or letter (in
quotes). For example...

Sub YourMacro()
MsgBox "First empty row in Column "G" = " & FirstEmptyRow("G")
End Sub

NOTE: This function does NOT consider a cell with a formula that is
displaying nothing ("") to be blank.

Rick

 

[Rick Rothstein \(MVP - VB\)]

That statement will give the wrong result if A1 is empty.

Rick

 

[kleysonr]

Thank you guys.

I also found 2 solutions:

#1
Worksheets(2).Range("A65536").End(xlUp).Row

#2
Function GetLastUsedCell(ColNum As Long) As Long

Const BottomRowNum = 65536

Dim LastUsedCell As Long
Dim UsedCellCount As Long
Dim LowerCellRange As Range
Dim CurrentCell As Range

' Ensure function recalculates each time worksheet is updated

Application.Volatile
With ActiveSheet

' Check that bottom cell is not empty

If Not IsEmpty(.Cells(BottomRowNum, ColNum)) Then
GetLastUsedCell = BottomRowNum
Exit Function
End If

' Estimate position of last used cell

LastUsedCell = .Cells(BottomRowNum, ColNum).End(xlUp).Row
If LastUsedCell = 1 Then

' Check cell as it may be empty. If so, return 0.

If IsEmpty(.Cells(1, ColNum)) Then
GetLastUsedCell = 0
Exit Function
End If
End If

Set LowerCellRange = Intersect(.UsedRange, _
.Range(.Cells(LastUsedCell + 1, _
ColNum), .Cells(BottomRowNum, ColNum)))

If Not LowerCellRange Is Nothing Then

' Check for hidden non-empty cells

UsedCellCount = _
Application.WorksheetFunction.CountA(LowerCellRange)
If UsedCellCount > 0 Then
Set CurrentCell = .Cells(LastUsedCell + _
LowerCellRange.Rows.Count, ColNum)

' Check vertically from bottom (of LowerCellRange)
' until first hidden non-empty cell is found

While IsEmpty(CurrentCell)
Set CurrentCell = CurrentCell.Offset(-1, 0)
Wend
LastUsedCell = CurrentCell.Row
Set CurrentCell = Nothing
End If
Set LowerCellRange = Nothing
End If
End With

GetLastUsedCell = LastUsedCell
End Function

 

[Rick Rothstein \(MVP - VB\)]

They will only do what you asked IF there are no blank cells in your column
of data, which is not what you asked for... you asked for "the number of the
FIRST empty row in a column", not the number of the row after the last piece
of data.

Rick

 

[TomPl]

Now I am thinking that "empty row" means all cells in that row (within the
specified range) must be blank. You might be able to tell that I was bored
today, because I did this:

Sub EmptyRow()

Dim ctr As Long
Dim ctc As Long
Dim eCells As Long
Dim rng As Range

'Pick a range, any range, usedRange if you like.
Set rng = ActiveSheet.Range("myRange")

For ctr = 1 To UBound(rng.Value)
eCells = 0
For ctc = 1 To UBound(rng.Value2)
If IsEmpty(rng.Cells(ctr, ctc)) = False Then
eCells = eCells + 1
End If
Next ctc
If eCells = 0 Then
GoTo IsEmpty
End If
Next ctr
ctr = ctr - 1
IsEmpty:
MsgBox ("Empty Row Is Row " & ctr)
End Sub

 

[Rick Rothstein \(MVP - VB\)]

That is an interesting read on the OP's question. On the off-chance you are
right, this function seems to work using less looping...

Function FirstFullyEmptyRow() As Long
Dim X As Long
On Error Resume Next
With Worksheets("Sheet1")
For X = 1 To .UsedRange.Rows.Count
Err.Clear
If Rows(X).EntireRow.SpecialCells(xlCellTypeBlanks).Count = _
.UsedRange.Columns.Count Then If Err.Number = 0 Then Exit For
Next
FirstFullyEmptyRow = X
End With
End Function

Rick

 

[TomPl]

Actually, I thought his question had already been answer, but I was bored.
I have not used the SpecialCells function but it looks interesting. I think
I will research it further, but that will have to wait until next week.

Thanks Rick

 

[Rick Rothstein \(MVP - VB\)]

Small correction in the function to account for the fact that the UsedRange
may not start at Column 1...

Function FirstFullyEmptyRow() As Long
Dim X As Long
On Error Resume Next
With Worksheets("Sheet3")
For X = 1 To .UsedRange.Row + .UsedRange.Rows.Count - 1
Err.Clear
If .Rows(X).EntireRow.SpecialCells(xlCellTypeBlanks).Count = _
.UsedRange.Column + .UsedRange.Columns.Count - 1 Then _
If Err.Number = 0 Then Exit For
Next
FirstFullyEmptyRow = X
End With
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, I wasn't sure if the question had been completely answered, but
you you are probably right, this method is more than likely not what the OP
wanted. However, I still think it is a valid question to develop code for...
Where is the first completely blank row in a set of data? By the way, be
sure to see my revised code and what it was that I revised... it will help
you when studying the SpecialCells (which are linked to the UsedRange).

Rick