Excel Vba: Select and trend routine

Question

So I have a table like this, assume Tidal Height is B1:

Tidal Time  Tidal Height
00:00:00    4.40
01:00:00    
02:00:00    
03:00:00    
04:00:00    
05:00:00    
06:00:00    2.00
07:00:00    2.50
08:00:00    3.00
09:00:00    3.50
10:00:00    4.00
11:00:00    4.50
12:00:00    
13:00:00    
14:00:00    
15:00:00    
16:00:00    
17:00:00    
18:00:00    2.10
19:00:00    2.56
20:00:00    3.02
21:00:00    3.48
22:00:00    3.94
23:00:00    4.40

So I need to do this:

1st Step: Select the first occupied cell from B2, and select up till (and including) the next occupied cell. Then run this code:

RangeToFill.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True

2nd Step: Deselect, then continue and select the cell before the next empty cell. Select up till (and including) the next occupied cell. Then run the trend code above.

I can do this by hand, but i'm unsure how to automate the process like this to take in account the changing data sets in different positions. Any help would be appreciated please!

See this - mattgemmell.com/what-have-you-tried ... Have you tried to write any code for this? Perhaps recorded a macro while doing it by hand? — Scott Holtzman
@ScottHoltzman The recorded macro would be useless. The only useful part would be RangeToFill.DataSeries which is already there. — GSerg
Maybe not useless, but I can see how the recorder may not help that much. I would think you would need to use a loop and start at B2 and use the Range.End(xlDown) method to get each set of data. However, as can be seen in your example data, you'll need to a check first if B3 is empty, since if you use the Range.End(xlDown) method, you will get the wrong range. — Scott Holtzman
Yeah, the recorder doesn't help at all, doesn't check if empty, and will only select the range that I set. I need this to be dynamically automatic up to B25. Range.End(x1Down) also doesn't fulfil this requirement — Savagefool
@Savagefool - Are you even trying to use the techniques I am providing? Or are you just shooting them down because you can't see right away how they would work? I have actually worked this out on my own and am happy to help you to help yourself, but if you shoot down ideas without even trying - as far as I can tell - how can I really help you? — Scott Holtzman

GSerg GSerg · Accepted Answer · 2015-09-22T13:19:48

Dim Bounds As Range
Set Bounds = Range("A1").CurrentRegion

Dim c As Range
Set c = Range("B2")

Do While c.Row < Bounds.Rows(Bounds.Rows.Count).Row
  If IsEmpty(c.Offset(1, 0).Value) Then
    Dim RangeToFill As Range
    Set RangeToFill = Application.Intersect(Range(c, c.End(xlDown)), Bounds)

    RangeToFill.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
    Set c = RangeToFill.Cells(RangeToFill.Cells.Count)
  Else
    Set c = c.End(xlDown)
  End If
Loop

Excel Vba: Select and trend routine

1 Answers