0

Title may not make complete sense. But description below should aid. I have similar data sets that come from one of our test machines that are no different unless by the number of samples given to the machine changes. There is a macro now that assumes 6 samples are to be measured but if more or less than 6 are done, it screws up the selection of data and then we get false numbers.

So I would like to edit it such that the VBA script intelligently iterates through a given range of cells defined by the first cell where the header for data is contained. This ideally returns the position of the last row and then the VBA script has knowledge of where to look for the specific numbers I want. i.e. cell E12 contains the header for the data I'm concerned with, and goes until E?? has no data in that cell

TL;DR - I need help with some code to give me the position of the last row that has data in it, starting from a certain cell.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Calderon315
  • 9
  • 1
  • 3
  • 1
    `position of the last row ` There's probably a gazillion google results for this XD – findwindow Dec 08 '15 at 19:41
  • 1
    like [here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). – Scott Craner Dec 08 '15 at 19:42
  • Thanks @ScottCraner. It's tough to know the exact phrases to search for. I did some prelim searching but couldn't find anything. That link looks promising. – Calderon315 Dec 08 '15 at 20:18

1 Answers1

0

I would recommend this: 

  Dim LastRow As Long
  With Sheets("Sheet1")
   LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
  End With

But bare in mind that:

  • this would start reading cells from the rows count of the sheet and go upwards until it bums into data filled cell only then it would return the index of that row.
  • this would return the last cell of data based on the column E. So if you have multiple columns and you're processing data for an entire sheet, make sure to choose that one column with the most data.
  • This would yield an error if you there are merged cells in the column.

You can then construct your intelligent data range.

it would be something like:

Dim r As Range
Set r = Range("A1:B" & LastRow)
Amen Jlili
  • 1,884
  • 4
  • 28
  • 51
  • thanks @Jelly and I realize this is just a part of the answer from the other link. I need a little help understanding what's going on though. I'm new to VBA and understand most but don't know it all. So 'LastRow' from your description is counting from the bottom up? Trying to find out how to best do this. I've tried the exact code you've given me and it works somewhat. I have a table that is outputted and one of the headers has filtering on so when I use that as a reference it gives me really odd numbers that don't make sense. When I got to the cell above, it works fine. – Calderon315 Dec 08 '15 at 21:11
  • My bad, I understand what's going on. It was by coincidence that I was counting _how many rows_ were within that range and it lined up for a few instances. This makes complete sense now and gives me what I initially asked for, which was the **what row** was the last line of data. – Calderon315 Dec 08 '15 at 22:24