Excel VBA - Finding Last Non-Empty Row Based on Column Header

3
votes

I am currently creating a reporting sheet for equipment for our company. This sheet will grab data from multiple Excel sheets and populate it based on our custom report template. So far I have managed to find on how to merge multiple sheets and find the column header. But now I'm stucked on how to find the last non empty value based on column header.

enter image description here

Attached in the picture above is the sample data. As you can see, the last non empty cell in each row is the average of the row. What I am trying to do is to find the header column(eg SVC525) and take the last non empty value of the column, which is average.

2
vbaexcel
last_row = ActiveSheet.Range("a1048576").End(xlUp).Row Change the Cell reference per your requirement – Barney
hi @barney, the code you gave is just return the row number. i want the last row value. sorry if i din't mention previously. – Jeeva
You should post your code so that we can tailor an answer to it. Anyway this is Excel VBA 101. Change x to your column Cells(Rows.Count, x).End (xlUp). You should watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) by WiseOwlTutorials – user6432984

2 Answers

3
votes

for the example given, column B, this should do the trick

Dim lastrow as Integer, val
lastrow = Range("B" & Rows.Count).End(xlUp).Row
val = range("B" & lastrow + 2).Value

to iterate through your rows(header) that is not empty is another story that you can easily search for.

1
votes

you may be after something like this

Dim svVal As String

svVal = "SVC525" '<--| set your header value to be searched for in row 1
With Worksheets("averages") '<--| change "Averages" tou your actual sheet name
    MsgBox .Cells(.Rows.count, .Range("A1", .Cells(1, .Columns.count).End(xlToLeft)).Find(what:=svVal, LookIn:=xlValues, lookat:=xlWhole).column).End(xlUp)
End With