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

BlueTrisch

New Member
Joined
Mar 2, 2016
Messages
2
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:

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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,789
Office Version
  1. 365
Platform
  1. Windows
Try this:

This macro will select the previous visible cell in the active cell column.
Code:
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.
Code:
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,789
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,148
Messages
5,835,680
Members
430,375
Latest member
datdog22

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?

Disable AdBlock

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
Top