Finding the last non blank cell in a row

Hello.

I need a cell function that tells me on which cell the last non blank cell is at.

I have 300 rows with numbers in column A. Row 301 - 310 are blanks and than there are some rows with numbers from 311 to X.

How do I find the last row in column A that are not blank? I want it to be dynamic.


/Anders

Hi,

Do you mean its value or is address ?

Carim

Its adress.

/Anders

Hi,
You can use the below code and get the desired result.
Create a ne module and write this VBA code.The message will the displayed aswell as it will select the last blank cell.


Sub find_out_lastblankcell()
Sheets("sheet1").Select
x = Range("A65536").End(xlUp).Row
For i = x To 1 Step -1
If Cells(i, 1) = "" Then
MsgBox "row" & i & " column " & 1
Cells(i, 1).Select
Exit For
End If
Next i
End Sub


Regards,
Roshin.P.P

Hello.

It is not really that kind of calulation I need.
If I select cell B3 and I want to know which row in Column A that has the last value. The values in Column A are dynamic and changes each day so I can not use any hard coded function.

/Anders

hi please ignore my above code.That will take you to the last blank cell.For last non blank cell use this code.(Selection will go there)

Sub find_out_lastnonblankcell()
Sheets("sheet1").Select
x = Range("A65536").End(xlUp).Row
Cells(x, 1).Select
End Sub

Regards,
Roshin.P.P

This VBA code is 100% dynamic.Just run the macro everytime u change the data.

cheeers!!
Roshin

Hi
This code will select from the active sheet.
Also you can change the column A to any column .The character marked in red has to be changed

Sub find_out_lastnonblankcell()
ActiveSheet.Select
x = Range("A65536").End(xlUp).Row
Cells(x, 1).Select
End Sub


Regards,
Roshin

Hi,

Please Login or Register  to view this content.

HTH
Carim