Excel VBA - Selection Routine

Question

SO I have a table like this:

Assume Tidal Time is column A, Tidal height column B

Tidal Time  Tidal Height
00:00:00    
01:00:00    
02:00:00    
03:00:00    
04:00:00    4.5
05:00:00    
06:00:00    
07:00:00    
08:00:00    
09:00:00    
10:00:00    2.1
11:00:00    
12:00:00    
13:00:00    
14:00:00    
15:00:00    
16:00:00    4.5
17:00:00    
18:00:00    
19:00:00    
20:00:00    
21:00:00    
22:00:00    1.9
23:00:00

What I need is a selection routine in excel-VBA like this:
Find the last non empty value (In this case 1.9)
Select last empty Value.
Select each cell up till the next non empty value. (In this case 4.5)
Use the following code to trend:
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Trend:=True
Select that cell again (4.5)
Select each cell up till the next non empty value (In this case 2.1)
Trend.
Select that cell it finished on (2.1)
Select each cell up till the next non empty value(In this case 4.5 at the top of the table)
Trend.

That's pretty much all that needs to happen. Can any shed some light on the process for this? I keep getting confused. It doesn't help that there are many different ways to do it either. Thanks in advance!

Excel Developers Excel Developers · Accepted Answer · 2015-08-24T11:54:33

You can use the Range.End() method to find the next (or last) non-empty cell. In your case you would work from the bottom up. So for example, to find the last non-empty cell (1.9):

Set LastCell = Activesheet.Cells(ActiveSheet.Rows.Count,2).End(xlUp)

Do While LastCell.Row > 2

    If LastCell.Offset(-1,0) = "" then
        Set NonEmptyCellAboveLastCell = LastCell.End(xlUp)
    Else
        Set NonEmptyCellAboveLastCell = LastCell.Offset(-1,0)
    End If

    If NonEmptyCellAboveLastCell.Row > 1 Then
        Set RangeToFill = ActiveSheet.Range(NonEmptyCellAboveLastCell, LastCell)
        RangeToFill.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True

        If NonEmptyCellAboveLastCell.Offset(-1,0) = "" then
            Set LastCell = NonEmptyCellAboveLastCell.End(xlUp)
        Else
            Set LastCell = NonEmptyCellAboveLastCell.Offset(-1,0)
        End If

    Else
        Set LastCell = ActiveSheet.Range("B1")
    End If
Loop

Note that the End method will jump to the next non-empty cell or to the last non-empty cell in a contiguous range of non-empty cells, so you need to check whether the adjacent cell is empty or not.

Excel VBA - Selection Routine

1 Answers