By Jeeva

2016-12-14 03:47:41 8 Comments

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.


@user3598756 2016-12-14 07:22:45

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

@user3598756 2016-12-16 06:31:16

@Jeeva, would you give any feedback?

@xtoybox 2016-12-14 04:34:40

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.

Related Questions

Sponsored Content

4 Answered Questions

[SOLVED] Detect non empty last cell location using Excel VBA

  • 2012-04-13 23:04:56
  • Mahender
  • 6038 View
  • 1 Score
  • 4 Answer
  • Tags:   excel excel-vba vba

23 Answered Questions

[SOLVED] Last non-empty cell in a column

2 Answered Questions

[SOLVED] Last not empty cell (column) in the given row; Excel VBA

  • 2011-02-02 09:09:21
  • user188962
  • 55978 View
  • 15 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] Excel VBA find row number based on criteria in two columns, no loop

1 Answered Questions

[SOLVED] Get last non-empty cell in Excel column

  • 2013-05-27 17:30:12
  • Indigo
  • 11465 View
  • 6 Score
  • 1 Answer
  • Tags:   c# excel

2 Answered Questions

[SOLVED] Excel Vba Macro to Delete Entire Row Based on Column Header

  • 2017-03-15 22:09:54
  • Defca Trick
  • 771 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] Find value in the last cell based on column header

1 Answered Questions

[SOLVED] How to get adress of last non empty cell in a excel using MACROS(VBA)

  • 2014-11-22 13:36:55
  • Nitish Bangad
  • 1064 View
  • 3 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

Sponsored Content