asked on

Excel VBA: find first non empty/populated cell in a column

Hello experts,

I am trying to figure out how to set up a procedure or a function to find the first populated/non empty cell in active column.

Example:
In the attached file I place the cursor in B45 in launch the procedure and I expect to have a row number 2.
The difficulty here is how to get the first non empty column of active column instead of used range.

Thank you for your help.
ExcelFirstColumn.xlsm

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you Martin

The idea is to have a generic approach not only for this specific case I am saying that due to the following:

For lngRow = 3 To lngLastRow

Open in new window

Why starting from 3 and why using a for loop?

I was thinking about something more simple and generic however I don't know how to improve the following:


Sub FirstNonEmptyRow()
    
    Dim ActiveColumn As String
    Dim FirstNonBlank As Range
   
    ActiveColumn = Split(ActiveCell.Address, "$")(1)
   
    Set FirstNonBlank = Range(ActiveColumn & 1)
   
    If Len(FirstNonBlank) = 0 Then
        Set FirstNonBlank = FirstNonBlank.End(xlDown)
    End If

    FirstNonBlank.Select

End Sub

Open in new window

Thank you for your help.

Martin Liss

Your Set FirstNonBlank = Range(ActiveColumn & 1) will always return A1 or B1, etc, depending on the cell selected.

Len(FirstNonBlank) = 0 will give the wrong results if the cell contains a series of spaces and I beieve also if it contains one or more non-printable characters. You should always use IsEmpty,

My approach is generic and even if there are hundreds of blank cells before the first non-blank one it will still seem instantaneous, but maybe I don't understand your use of "generic".

SOLUTION

Joe Winograd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joe Winograd

oops...while I was writing and testing the code, I saw when I hit the Submit button that Martin responded. Feel free to ignore my post. :)

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

@Subodh:

Thank you for this proposal.

I am doing some testings but I don't get the expected result.

1.I go to C23

2.I launch the procedure

3.But I don't have the go to C2. Position remains at C23.

Thank you in advance for your help.

ExcelFirstColumn.xlsm

Luis Diaz

ASKER

@Martin: I tested your proposal and it works

What I don't understand is the loop:

For lngRow = 3 To lngLastRow

Why start looping at 3?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

@Subodh: You are totally right I didn't use the debug option for this case (+f8).

Sorry for that.

I tested both proposals and they work!

Thank you again for your help.

Subodh Tiwari (Neeraj)

You're welcome Luis! Glad it worked as desired.

Martin Liss

What I don't understand is the loop:

That's because I assume that the first two heading rows will always be there and so to find the first non-empty data cell, my loop needs to start at 3.