Return value of first non blank cell above active cell

Y

yarate

New Member
Joined
Dec 1, 2011
Messages
4
Hi everyone!

I've tried to find the answer for that question but it doesn't seem that it has ever been quite resolved. So here I am, trying to get resolution on it!

I want to be able to return the value of the first non blank value above a given cell (as parameter).

Example
A1:
A2: x
A3:
A4: y
A5:
A6:

myfunc(A6) should return "y"
myfunc(A4) should return "y"
myfunc(A3) should return "x"
myfunc(A1) should return nothing

Details:
*Values are a mixed of numeric and text (they are account #) such as H2345435

I hope you guys can help out
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Hi yarate and welcome to the board..

try these..

Code: 
Sub first_non_blank_cell_above_dynamic_selection()
'above user selection
Dim oRangeSelected As Range
Set oRangeSelected = Application.InputBox("Please select a cells!", _
"Please select a cell", Selection.Address, , , , , 8)
rw = oRangeSelected.Row
MsgBox Cells(rw + 1, "A").End(xlUp).Value
End Sub
Code: 
Sub first_non_blank_cell_above_active_cell()
'above selected cell
rw = Selection.Row
MsgBox Cells(rw + 1, "A").End(xlUp).Value
End Sub
 
Upvote 0
Code: 
Function MyFunc(rng As Range) As String
    Application.Volatile
    MyFunc = rng.End(xlUp).Value
End Function

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF99;;">y</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">x</td><td style="text-align: right;;"></td><td style="background-color: #FFFF99;;">x</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFFF99;;">x</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">y</td><td style="text-align: right;;"></td><td style="background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=myfunc(<font color="Blue">A6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=myfunc(<font color="Blue">A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=myfunc(<font color="Blue">A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=myfunc(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:
Upvote 0
ravi4ever said:
Hi yarate and welcome to the board..

try these..

Code: 
Sub first_non_blank_cell_above_dynamic_selection()
'above user selection
Dim oRangeSelected As Range
Set oRangeSelected = Application.InputBox("Please select a cells!", _
"Please select a cell", Selection.Address, , , , , 8)
rw = oRangeSelected.Row
MsgBox Cells(rw + 1, "A").End(xlUp).Value
End Sub
Code: 
Sub first_non_blank_cell_above_active_cell()
'above selected cell
rw = Selection.Row
MsgBox Cells(rw + 1, "A").End(xlUp).Value
End Sub
Click to expand...

Thanks for the answer! I've tried both and the only problem is that I receive the answer ( a prompt message appears with the correct value), but the value itself doesn't get entered in the selected/active cell.

Also I like the 2nd code better as it eliminates the step of Excel asking me to confirm which is the active cell (parameter) and assumes it is the one from where I ran the macro
 
Last edited:
Upvote 0
AlphaFrog said:
Code: 
Function MyFunc(rng As Range) As String
    Application.Volatile
    MyFunc = rng.End(xlUp).Value
End Function


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff99">y</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff99">x</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff99">x</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff99"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left">=myfunc(A6)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=myfunc(A4)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=myfunc(A3)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=myfunc(A1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Click to expand...
I think, A4 should result in "y" and not "x" as per post#1
Code: 
Function MyFunc(rng As Range) As String
    Application.Volatile
    If rng.Value <> "" Then
    MyFunc = rng.Value
    Else
    MyFunc = rng.End(xlUp).Value
    End If
End Function
 
Upvote 0
taurean said:
I think, A4 should result in "y" and not "x" as per post#1
Code: 
Function MyFunc(rng As Range) As String
    Application.Volatile
    If rng.Value <> "" Then
    MyFunc = rng.Value
    Else
    MyFunc = rng.End(xlUp).Value
    End If
End Function
Click to expand...

Thanks for the answer! Yes, that's correct. Also, I'm working in one column and only one, so I'd like the result value to be entered in the same column as the range of values. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Pull First Text Value in a Column
Replies
6
Views
671
CrushFabrication
C
J
  • Question Question
Formula to find first blank cell and return another value
Replies
4
Views
681
Fluff
Fluff
E
  • Solved
Find lowest LEN difference & return contents of cell to left
Replies
5
Views
96
Sufiyan97
Sufiyan97
F
  • Question Question
Formula to insert a picture based on various cell values, w/out VBA, Macros, etc.
Replies
4
Views
1K
Domenic
D
P
  • Question Question
Finding the next empty Row always seems to ignore the first row of a table. Even if empty. Please Help.
Replies
3
Views
575
Alex Blakenburg
A

Forum statistics

Threads
1,223,767
Messages
6,174,395
Members
452,561
Latest member
amir5104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top