• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

First and Last value of an array

S

sandeshnegi

New Member
If in a column A I have certain ranges with values and then few empty cells and again few cells with data and so on. So for example if A1-A3 is filled, A4 - A6 is blank, A7 - A9 is filled, A9-A12 is blank, A13 - A15 is filled, then return
First Filled Cell = A1
Last Filled Cell in continuous range= A3
Second Filled Cell = A7
Second Filled Cell in continuous range = A9
And so on
 
You please try it.

>>> use code - tags <<<
Code: 
Public Sub s()
j = 1
k = 1
For i = 1 To ActiveSheet.UsedRange.Rows.Count

If Range("A" & i) <> "" And Range("A" & i + 2) <> "" Then

Cells(j, 2) = Range("A" & i)
Cells(k, 3) = Range("A" & i + 2)

j = j + 1
k = k + 1

End If
Next i

End Sub
 
Last edited by a moderator:
sandeshnegi
You're expecting formula solution ( Ask an Excel Question -forum ).
Please attach an Excel file,
which shows Your case
with expected result (even manually solved).
 
See the table at G9.
Not formula but Power Query
Change the data in the table on the left, then right-click the table at G9 and choose Refresh to update the results.

ps. You do realise that the second bunch of distances seems to overlap Car2 and Car3?, so just in case you wanted to include that differentiation, there's another query table at cell M2.
 

Attachments

  • Chandoo48419DistanceTime.xlsx
    23.1 KB · Views: 4
Last edited:
thanks
it works for me, but is it possible if the values are in rows instead of columns & could you please guide the steps you did to get these results
 
sandeshnegi
Seems You accept other than formula-solution.
Check this
Press [ SUM ]-button to get those two sums.
You can add as many rows of data as You would like to.
Is there clear logic with those Date-values which You've expected? ... last & the first
 

Attachments

  • DistanceTime.xlsb
    18 KB · Views: 2
vletm said:
sandeshnegi
Seems You accept other than formula-solution.
Check this
Press [ SUM ]-button to get those two sums.
You can add as many rows of data as You would like to.
Is there clear logic with those Date-values which You've expected? ... last & the first
Click to expand...
thanks
this code works but is it possible to get the max date in the cells G7 for the second continuous range & the location as well. For first continuous range the max date is ok.
 
Last edited:
Sorry my data is bit fussy, There in one more query, if I could get two more information in row I & J. File attached

Also I am not able to run the code in some other file it open back the current file.
 

Attachments

  • DistanceTime (1).xlsb
    30.3 KB · Views: 2
sandeshnegi
# What would be those two more informations?
... should there be somewhere ... pillar?
... or are Your expected results empty?

# How did You try to run the code?
What have You done before You tried to run the code?
 
More information, I have written in Column "I" as Starting Pillar & "J" as End Pillar, I would have used index match or lookup but there are many duplicates dates. So every time we get the sum it tells the starting & end pillars. Attaching the new file herewith.

I just copied your code in another sheet, may be I shall try once again.
 

Attachments

  • DistanceTime (1).xlsb
    30.4 KB · Views: 2
Last edited:
Check table at cell L5. Refresh as before.
 

Attachments

  • Chandoo48419DistanceTime (1).xlsx
    50.9 KB · Views: 1
Last edited:
hi can you advise if this table can be done without power query, also if it is possible to get the earliest dates by which the 500m target is achieved (in continuation) & then the sequence of the remaining in set of 500m.
 
You must log in or register to reply here.
Back
Top