Selecting range of Range object-CodePudding

I have got a sheet with holidays of 5 countries in 5 columns (each row is a date of holiday for given country) and I want to use it for Application.WorksheetFunction.WorkDay function

Sub Func()
   Dim holidays As Worksheet
   Set holidays = Sheets("Holidays")
   Dim lastHolidayRow As Long
   
   lastHolidayRow = holidays.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   holi = holidays.Range("A2:E" & lastHolidayRow).Value

   previous_working_day = Application.WorksheetFunction.WorkDay("19.02.2022", -1, holi(3, :)) 

End Sub

but I have no idea how to choose entire 3rd column (in many programing languages it would be holi(3, :)) for WorkDay function.

Also is my lastHolidayRow the best way to select all data from that sheet? Some countries has less holidays than others, so am I selecting the last last row correctly?

for example if my date is 01.01.2010 then WorkDay func should return 02.01.2010 (it's DD-MM-YYYY)

CodePudding user response：

Try this:

Sub Func()
Dim holidays As Worksheet
Set holidays = Sheets("Holidays")
Dim holi As Range
Dim previous_working_day As Date

Set holi = holidays.Range("A1").CurrentRegion
lastHolidayRow = holidays.Rows.Count

previous_working_day = Application.WorksheetFunction.WorkDay("19.02.2022", -1, holidays.Range("A2:A" & lastHolidayRow))

Set holi = Nothing

End Sub

Notice the part that says holidays.Range("A2:A" & lastHolidayRow). This is referenced to PLN only. If you want to switch to other country change both A to whatever column you want. For EUR it would be holidays.Range("C2:C" & lastHolidayRow) and so on.

CodePudding user response：

Try Range("C:C") if I understood your question correctly

Sub Func()
   Dim holidays As Worksheet
   Dim holi as Range
   Set holidays = Sheets("Holidays")
   Dim lastHolidayRow As Long
   
   lastHolidayRow = holidays.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Set holi = holidays.Range("A2:E" & lastHolidayRow)

   next_working_day = Application.WorksheetFunction.WorkDay("19.02.2022", -1, holi.Range("C:C")) 

End Sub