How to get the value of the last non-empty cell in a row?

Question

Here is a sample spreadsheet

Sample table

I want to select a range (ex: A2:E2) and get the value 1. If I select A4:E4 I will get the value 1; and so on. Is there any way to achieve this with a macro?

Another thing I would need is the value in the first row of the corresponding column. Ex:

If I select A2:E2 I want to get n (since 1 is in column E), if I select A4:E4 I want to get v (since 1 is in column C).

possible duplicate of [Last not empty cell in row; Excel VB](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vb). — brettdj, Jan 27 '12 at 00:31
Yeah, this is such a multiple duplicate. And there are tons of questions about getting the last non-empty cell in a column, too -- and the answer is practically the same. @user1112251, you gotta start using the search function. — Jean-François Corbett, Jan 27 '12 at 12:45

Tony Dallimore · Answer 1 · 2012-01-26T19:01:44.440

2

The following code should do what you want. Note: I am not selecting anything since this slows things down to no advantage.

Dim ColLast as Long
Dim ValueColLast as String
Dim HeadColLast as String

With Sheets(xxxxx)

  ColLast = .Cells(2,Columns.Count).End(xlToLeft).Column
  ValueColLast = .Cells(2, ColLast).Value
  HeadColLast = .Cells(1,ColLast).Value 

End With

Cells(Row, Column) addresses the cell identified by Row and Column in the active worksheet. Row must be a number. Column can be a number (eg 1 or 104) or a column identifier (eg "A" or "CZ")

.Cells(Row, Column) addresses the cell identified by Row and Column in the worksheet identified in the With statement. Note the leading dot.

.Cells(2,Columns.Count) addresses the last cell in row 2 since Columns.Count given the number of columns per worksheet for the current version of Excel. `Rows.Count does the same for rows.

Assuming .Cells(2,Columns.Count) is blank, .Cells(2,Columns.Count).End(xlToLeft) finds the next cell in the specified direction with a value. This is the VBA equivalent of Ctrl+LeftArrow. xlToRight, xlUp and xlDown allow you to move in the other directions. .Cells(Rows.Count,"A").End(xlUp) gives the last used row in Column A.

.Cells(2,Columns.Count).End(xlToLeft).Column gives the number of the last used column in row 2.

ValueColLast = .Cells(2, ColLast).Value sets ValueColLast to the value of the last cell in Row 2. I have defined ValueColLast as a String because, if I assumed it was a number and defined it as Long, I would get an error if it was not.

HeadColLast = .Cells(1,ColLast).Value sets HeadColLast to value in Row 1.

Hope this all makes sense.

edited Jan 26 '12 at 19:01

answered Jan 26 '12 at 17:30

Tony Dallimore

12,335
7
32
61

You might explain your code with inline comments, to avoid having a cluttered answer. Also, if you don't select anything how will your code know what values to return? – JimmyPena Jan 26 '12 at 20:02
@JP. I assume (perhaps unfairly) the OP has little knowledge of the syntax so wanted to provide a detailed explanation. I will use inline comments to explain why I am doing something but not to explain the syntax. Perhaps I was unclear; I should have said I was not selecting worksheets or ranges or cells as in no `Range(X).Select` `Value = ActiveCell.Value`. – Tony Dallimore Jan 26 '12 at 22:21
Using the arrow key shortcuts to determine range limits does rqeuire two tests for completeness a) testing that cell used to start the search from is empty [uncommon as this would normally mean the entire row/column if full] b) testing that the cell found is not empty [more common as rows and columns are often completely empty]. The `Find` approach in the link I posted gets around this checking process – brettdj Jan 27 '12 at 00:35
@brettdj. _I cannot get my responses to brettdj and JP in anywhere close to 500 characters so two comments that need to be read as one_. I find I learn best if I have to struggle with a problem. Whether I find or am given the solution, I will remember it and a bit more which guides my approach to answering questions: the solution and a bit more. But this approach breaks down when finding the last used cell in a range because every technique (find, arrow key shortcuts, special cells) fails in one or more situations. – Tony Dallimore Jan 27 '12 at 10:39
@JP. Do I explain the solution or when it might fail because to do both seems too much? Here I gave an over-the-top explanation of the arrow key shortcut approach and ignored failure conditions. Wrong? Probably in this case? For my own use I have a workbook with some 20 worksheets each of which exhibits a different boundary condition and a routine that works for all of them. I have learnt a lot about boundary conditions since I wrote that routine. Perhaps I should review it and publish it. – Tony Dallimore Jan 27 '12 at 10:39

score 1 · Accepted Answer · answered Jan 26 '12 at 20:52

Ok, here is a function which you can use as a formula in the workbook. It also addresses both your questions. :)

Paste the below code in a module.

SYNTAX/USAGE

=MyFunc(Cell Range, Arg)

Cell Range: (Required) A range such as A2:E2

Arg: (Required) Can take a value of "H" Or "R". "H" will give you the column Header and "R" will give you the row value

Example

=MyFunc(A1:E2,"H") will give you "n" AND

=MyFunc(A1:E2,"R") will give you "1"

Option Explicit

Function MyFunc(rng As Range, stype As String) As Variant
    Dim MyArray() As String
    Dim sCol As Long

    If stype = "" Or (UCase(stype) <> "H" And _
    UCase(stype) <> "R") Then Exit Function

    With rng
        MyArray = Split(.Address, "$")

        sCol = Cells(Val(MyArray(UBound(MyArray))), _
        Columns.Count).End(xlToLeft).Column

        If UCase(stype) = "H" Then
            MyFunc = Cells(1, sCol)
        ElseIf UCase(stype) = "R" Then
            MyFunc = Range(MyArray(UBound(MyArray) - 1) & _
            MyArray(UBound(MyArray))).Value
        End If
    End With
End Function

Note: This is just a general code to show you how it works and it doesn't handle any errors at the moment.

You solution is great, but when I use "H" to get the headers, It only returns 0. — user1112251, Jan 27 '12 at 03:11
I had tried the code before posting. Hope you are not entering the formula in the same row as the data. For example, if I take the above example then you cannot put the formula anywhere in Rows 1-5. Of Course this can be taken care of with few more lines in the above code — Siddharth Rout, Jan 27 '12 at 03:25

How to get the value of the last non-empty cell in a row?

2 Answers2