Excel Question

[WarBus]

You have a column with #s and empty cells after.
Ex.
1
2
3
4
_
_

At the end of the column you want to have the last # enterered but do not know what cell it would be.
Ex.
1
2
3
4
_
_
4

How can this be done?

Thanks, WarBus

[IHateKeithSmart]

War:

Will it always be "some unknown # of cells with numbers followed by some unknown # of cells that are blank" or will they be interspersed (e.g. 3 cells with numbers, 3 with blanks, 2 with numbers, 1 blank, etc.)?

The latter will be more difficult, but the former should be relatively simple. The isblank(<cell>) and isnumber(<cell>) functions look for blanks and numbers, respectively.

[WarBus]

The blank cells will only be after the #s.

Thanks,
WarBus

[WarBus]

I've tried to make a formula with isblank and isnumber. I can get it to work with a handful of cells. I do not know how to make a formula that works with 60 cells in a column.

Any ideas out there?

I'm not very proficient with excel.

Thanks,
WarBus

[CORed]

Does the number have to be at the bottom? What I usually do if I want the sum, average min, max, etc. of a column that will have an unknown number of entries is put it at the top.

[WarBus]

It does not need to be at the bottom. The problem is that I need the last cell entry but do not know which cell and the entry is a random number.

Thanks,
WarBus

[mtgordon]

There's an easy but very ugly way to do it. To the right of ever cell you can have the value of the cell be equal to the cell beside it if the cell below it is blank. Then you could sum up that column.

I'm tired and I'm not sure if that made much sense so I'll try again. Say that all of your #s are in column A. You could have B1 = A1 if A2=="". Then you can sum column B which will be all blanks except for the cell in column B that is beside the last number in column A and that cell will have the same value as the one in column A.

Hmmmmm, I wonder if that was any clearer. I'll check again tomorrow and see if you have it figured out. If you think my idea sounds reasonable but can't quite understand what I'm rambling about, let me know.

[IHateKeithSmart]

This sounds like a good solution (kludgy, but works). I was going to suggest a little vba, but that probably won't do you any good if you're not that comfortable with excel.

[WarBus]

I found a generic solution that is supposed to work using vba but have never used vba. I have no idea how to apply it.

ex.

Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub

So, I am going to try mtgordon's idea today. Pretty sure I can make that work.

Thanks,
WarBus

[Link774]

I think the code you posted would just select the last entry when the macro is executed. You need a function, not a subroutine. This should do the trick (grr, the board screwed up my formatting):

Function LastConsecutiveNonBlankValue(range)
lastEntry = ""
For Each entry In range
If entry.Value = "" Then
LastConsecutiveNonBlankValue = lastEntry
Exit For
End If
lastEntry = entry.Value
Next
If LastConsecutiveNonBlankValue = "" Then
LastConsecutiveNonBlankValue = lastEntry
End If
End Function

This is just a custom excel function, you'd probably want to place it in the workbook in which it is needed by pressing Alt+F11 to open the VB editor, then selecting the excel file you're working on from the list on the left, then going to Insert->Module. Double click the new module to open it, then paste that code in. Alt+F11 will get you back to your sheet, where you can use this function as you would any excel function. Let me know if you have any problems!

--Link

[WarBus]

Thank you,
That worked perfectly.

One more question.

I didn't realize that some less used columns would have blank cells intermixed with numbers.
What would be the function that finds the last entry in a column under these conditions?

[Link774]

That's even easier:

Function LastNonBlankValue(range)
For Each entry In range
If entry.Value <> "" Then
LastNonBlankValue = entry.Value
End If
Next
End Function

Enjoy!
-Link

[WarBus]

Works great!
Thank you much.

WarBus