Business day only

[JZB]

Hi Guys

I am creating a holiday calendar. I am trying to go down a cell range and delete any dates that are non business.

I could try:

For nloop = 1 to nlastrow

If activecell = vbsunday or activecell = vbsaturday then
activecell.entirerow.delete

Else

Activecell.offset(1,0).select

End if

Next nloop

But i am sure there is an easier way to do it that i just have not come accross. :dunno

I would also like to include festive holidays like easter, christmas etc

Thanks for the help in advance

Cheers

Jon

[BrianMH]

Can I ask why your wanting to make a list? Will you be using it for another function? You could always use the function workday.

http://office.microsoft.com/en-gb/excel/HP052093391033.aspx

Just start with the first day of the year. Have a list of holidays in a named range holidays and add 1 day and just drag this down.

A1 = the first day
A2 = workday(A1,1,holidays)

[mdmackillop]

Put your holidays in a range "Holidays"

Option Explicit
Sub Dates()
Dim i As Long, cel As Range, c As Range
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Weekday(Cells(i, 1)) = 6 Or Weekday(Cells(i, 1)) = 7 Then
Cells(i, 1).Delete shift:=xlUp
End If
Next
For Each cel In Range("Holidays")
Set c = Columns(1).Find(cel)
If Not c Is Nothing Then c.Delete shift:=xlUp
Next
End Sub

[JZB]

to get a clear view of the whole year, i would like to have 365 rows of days (column A) with names across the first row (b1, C1 etc.)

ideally i would like to go through the column a and delete all non business days, then create a two row separation between each different month.

Cheers

[BrianMH]

With what names? Is that relevant to the dates or just other information you have for your own use?

[Bob Phillips]

Why not just use a formula of

A2: =WORKDAY(DATE(YEAR(TODAY()),1,0),1)
A3: =WORKDAY(A2,1)

and just copy A3 down