F
Frank
I have a worksheet with dates and data. The date column only includes
business days from different countries.
I need to retrieve the first available business days for every month
and every year.
Here is my lousy yet working code.
I parse the A column, where the date reside) into 3 (month, day and
year) and sort the worksheet by day (column b). Then I autofilter each
month, autofilter each year and finally copy the results. I loop for
each month and each year.
It works but looks there must be a better and faster way.
BTW, I’m not a programmer, which explains the complicated code:
Application.ScreenUpdating = False
Columns("B:C").Insert Shift:=xlToRight
Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, TextQualifier:=xlNone, OtherChar:="/"
Columns("A:C").NumberFormat = "General"
Range("A1") = "month"
Range("B1") = "day"
Range("C1") = "year"
c = 1 'month
yhigh = Application.Max(Columns("C:C")) + 1 'the highest year
Range(("A1"), Range("A1").SpecialCells(xlLastCell)).Select
With Selection
.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
Do Until c = 13 '12 = December
ylow = Application.Min(Columns("C:C")) 'reset the lowest year
.AutoFilter Field:=1, Criteria1:=c
Do Until ylow = yhigh
.AutoFilter Field:=3, Criteria1:=ylow
Selection.Copy
Sheets("data1").Select
ActiveCell.PasteSpecial xlPasteAll
ActiveCell.EntireRow.Delete
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell,
ActiveCell.SpecialCells(xlLastCell)).Delete
Sheets("data").Select
ylow = ylow + 1 'go through each year for specific month
(c)
Loop
c = c + 1 'goto next month
Loop
End With
Sheets("data1").Select
Range(("B1"), Range("B1").End(xlDown)) = 1 'each first day of the
month must equat 1
Range(("A1"), Range("A1").SpecialCells(xlLastCell)).Sort
Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("A1"),
Order2:=xlAscending, Header:=xlYes
business days from different countries.
I need to retrieve the first available business days for every month
and every year.
Here is my lousy yet working code.
I parse the A column, where the date reside) into 3 (month, day and
year) and sort the worksheet by day (column b). Then I autofilter each
month, autofilter each year and finally copy the results. I loop for
each month and each year.
It works but looks there must be a better and faster way.
BTW, I’m not a programmer, which explains the complicated code:
Application.ScreenUpdating = False
Columns("B:C").Insert Shift:=xlToRight
Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, TextQualifier:=xlNone, OtherChar:="/"
Columns("A:C").NumberFormat = "General"
Range("A1") = "month"
Range("B1") = "day"
Range("C1") = "year"
c = 1 'month
yhigh = Application.Max(Columns("C:C")) + 1 'the highest year
Range(("A1"), Range("A1").SpecialCells(xlLastCell)).Select
With Selection
.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
Do Until c = 13 '12 = December
ylow = Application.Min(Columns("C:C")) 'reset the lowest year
.AutoFilter Field:=1, Criteria1:=c
Do Until ylow = yhigh
.AutoFilter Field:=3, Criteria1:=ylow
Selection.Copy
Sheets("data1").Select
ActiveCell.PasteSpecial xlPasteAll
ActiveCell.EntireRow.Delete
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell,
ActiveCell.SpecialCells(xlLastCell)).Delete
Sheets("data").Select
ylow = ylow + 1 'go through each year for specific month
(c)
Loop
c = c + 1 'goto next month
Loop
End With
Sheets("data1").Select
Range(("B1"), Range("B1").End(xlDown)) = 1 'each first day of the
month must equat 1
Range(("A1"), Range("A1").SpecialCells(xlLastCell)).Sort
Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("A1"),
Order2:=xlAscending, Header:=xlYes