CODEWITHSUNDEEP
excelvba
hakandeep
hakandeep

Reputation: 51

How to find first cell that contains data with respect to column?

I am using 

lastRowIndex = .Cells(.Rows.Count, "A").End(xlUp).row

to find the last row with data.

How do I find the first row? I found some code with activesheet.usedrange but it does not work for me since for b column the first data starts at second row, but in A column it starts at 4th row. I need a function that finds me the number 4.

Upvotes: 1

Views: 108

Answers (2)

user3598756
user3598756

Reputation: 29421

an alternative version without the use of CountA() is the following:

Function firstrow2(col As Range) As Long
    Dim f As Range
    Set f = col.Find("*", after:=col.Cells(col.Parent.Rows.Count))  '<--| look for any value in given column from its row 1 (included) downwards
    If Not f Is Nothing Then firstrow2 = f.Row '<--| if found then return its row index
End Function

which is returning 0 if there is no data in passed column

should you make it a little more robust and handle a wrong passed range (not an entire column or wider then a column) you can use the following:

Function FirstRow(col As Range) As Long
    Dim f As Range
    With col.Columns(1).EntireColumn
        Set f = .Find("*", after:=.Cells(.Rows.Count))  '<--| look for any value from row 1 (included)
    End With
    If Not f Is Nothing Then FirstRow4 = f.Row '<--| if found then return its row index
End Function

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166980

Dim col As Range
Set col = Columns(10)

If Application.CountA(col) > 0 Then
    Debug.Print "first data: " & col.Find("*", after:=col.Cells(Rows.Count)).Row
Else
    Debug.Print "no data"
End If

Upvotes: 1

Related Questions