CODEWITHSUNDEEP
vbaexcel
MG78
MG78

Reputation: 219

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

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.

Upvotes: 3

Views: 12551

Answers (2)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

xtoybox
xtoybox

Reputation: 57

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.

Upvotes: 3

Related Questions