How to find the last non-empty cell above active cell with VBA?

[BlueTrisch]

Hi, I'm new to the forum and this is my first post, although just reading various threads in the past helped me a lot.
But now I am struggling to find a solution and hope you can help me.

Let's say I have 3 columns:

A - start date
B - end date
C - no of working days

and about 200 rows.
It is very likely that not all cells are filled, so that there are empty cells. These empty rows will automatically be hidden.

I want to calculate the end date, after start date and no of working days are filled out. Because the cursor jumps in the next cell below, this becomes the active cell.
Therefor I used the following code:

Sub CalcDates()
Dim Date1 As Date, Date2 As Date
Dim Days1 As Integer
Dim Holiday1 As Range
 
    'specify start date
    Date1 = ActiveCell.Offset(-1, -2).Value
    'specify no of working days
    Days1 = ActiveCell.Offset(-1, 0).Value - 1
    'specify holidays
    Set Holiday1 = Sheets("Holidays").Range("Hol")
    'calculate end date
    Date2 = WorksheetFunction.WorkDay(Date1, Days1, Holiday1)
    'fill end day-cell
    ActiveCell.Offset(-1, -1).Value = Date2
  
End Sub

This works fine as long as the row above the active cell is the one, where data has been entered lately.
If for example C5 gets filled with data and row 6 is empty (and therefor hidden), C7 becomes the active cell and the Offset function doesn't work anymore.

Is there a way to select the last cell above the active cell which includes data?

Thanks in advance!
BlueTrisch

 

[Akuini]

Try this:

This macro will select the previous visible cell in the active cell column.

Sub toVisible()
' go to previous visible cell
Dim r As Range
Set r = ActiveCell.Offset(-1, 0)
Do
If r.EntireRow.Hidden = True Then
Set r = r.Offset(-1, 0)
End If
Loop Until r.EntireRow.Hidden = False
r.Activate
End Sub

This macro will select the previous visible non blank cell in the active cell column.

Sub toNonBlank()
' go to previous non blank cell
Dim r As Range
Set r = ActiveCell
Set r = Range(Cells(1, r.Column), Cells(r.row - 1, r.Column)).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
r.Activate
End Sub

 

[BlueTrisch]

Thank you very much!
That works perfectly!

 

[Akuini]

You're welcome.