# Last row in data using offset

#### 99moorem

##### New Member
Hi Mr Excel,

I have been trying to figure this out for ages! and I know it is more less what I am trying to do but I can not get it work correctly.

There are two aims of this 1 to find the begining date and 2 to find the end date of data. Now let me be clear im not looking for the date's only the offset numbers of the start/end of the data. From that I can get to the date's quite easily.

I have the start of the data using
=MATCH(TRUE,INDEX(\$P11:\$GT11>0,0),0)

This for the middle row and returns me a value of 8 which is exactly what I expect, this formula is in A11

The next part, finding the end of the data, I can not work out, this is as far as I got
=MATCH(TRUE,INDEX(OFFSET(\$P11:\$GT11,0,A11)>0,0),0)

Sorry for the silly way this looks on my spreadsheet I have the dates aligned horizontally

Anyway below is an example of my data, There are columns of data to the left which have no blanks in them. On my sheet this data is an extract from P9:BN12 but my whole data goes up to GT45. There is only ever one line of data in each row

Im pretty sure this is just a formula and I am just being silly but any help would be appreciated
 02/06/2014 09/06/2014 16/06/2014 23/06/2014 30/06/2014 07/07/2014 14/07/2014 21/07/2014 28/07/2014 04/08/2014 11/08/2014 18/08/2014 25/08/2014 01/09/2014 08/09/2014 15/09/2014 22/09/2014 29/09/2014 06/10/2014 13/10/2014 20/10/2014 27/10/2014 03/11/2014 10/11/2014 17/11/2014 24/11/2014 01/12/2014 08/12/2014 15/12/2014 22/12/2014 29/12/2014 05/01/2015 12/01/2015 19/01/2015 26/01/2015 02/02/2015 09/02/2015 16/02/2015 23/02/2015 02/03/2015 09/03/2015 16/03/2015 23/03/2015 30/03/2015 06/04/2015 13/04/2015 20/04/2015 27/04/2015 04/05/2015 11/05/2015 E E E E E E E E E E M E E E E E E E E E M E E E E E E E E E M E E E E E E E E M E E E E M E E E E M E E E E M E E E E M E E E E M E E E E E E E E E E

<TBODY>
</TBODY>

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
This shows me the last date is in the 7th. column.

Excel 2010
ABCDEFGH
18Friday August 1, 2014Monday December 1, 2014Saturday October 4, 2014Tuesday April 1, 2014Saturday March 1, 2014
19
207
Sheet3
Cell Formulas
RangeFormula
B20=MATCH(9.99999999999999E+307,18:18)

I think this formula works up until you find a blank cell, so for my example in row 3 I have blank rows that need to be accounted for.

I dont know this formula could be coupled with the offset formula? as I can find the first piece of information as a number

I think this formula works up until you find a blank cell, so for my example in row 3 I have blank rows that need to be accounted for.

I dont know this formula could be coupled with the offset formula? as I can find the first piece of information as a number

A18 and F18 are blank in my example and it ignored them, what do you mean works up until you find a blank cell?

You said you have blank rows in row 3, did you mean to say you have blank cells?

Last edited:
Hi,

Yes I did mean blank cells.

Sorry I think I read your example wrong.

I can see your example that it is missing the blanks and finding the last data, I tried to enter your data and formula exactly and seem to get a #N/A from the formula?

In B20 =MATCH(9.99999999999999E+307,18:18)

And the data is defiantly in rows 18.

I am using Excel 2010 currently (this is not my development platform or the end users so if this is the problem I will try it on Excel 2007 when I get home).

Thanks for your help and sorry for not reading your example correctly.

Marc

Hi,

Could not get the match(9.9999E+307 to work correctly so instead I have built some vba to do this for me, didnt particulaly want to use vba for this but it may be the quickest solution for me (in terms of development time that is)

Code:
``````Sub Update_End_Of_TC_Data()
Dim Col As Long
Sheets("TC Forecast").Select
Col = Range("F9").End(xlToRight).Column
For Each cell In Range("A10:" & Range("F10000").End(xlUp).Offset(0, -5).Address)
Start = 0
For Each c In Range(cell.Offset(0, 17).Address & ":" & Col_Letter(Col) & cell.Row)
If Start = 0 And c.Value = "E" Then
cell.Value = c.Column - 1
End If

If c.Value = "E" Then
Start = 1
End If

If Start = 1 And c.Value = "" Then
cell.Offset(0, 1).Value = c.Column - 1
Exit For
End If
Next
If Start = 0 Then
cell.Value = ""
End If
Next
End Sub``````

Thanks for all your help.

Marc

Replies
5
Views
81
Replies
5
Views
115
Replies
2
Views
278
Replies
27
Views
433
Replies
1
Views
93

1,203,125
Messages
6,053,655
Members
444,676
Latest member
locapoca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back