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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
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
2,936
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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