VBA - search every third column for data

[macroos]

I need a VBA in column F that grabs dates from every third column starting from I, L, O, and so on.
Columns I, L, O, etc has dates (all last day of the month) in ascending order.

For example below, column F will start with the oldest date, 2/28/17, all the way to most current date, 4/30/18.
Although there is no 7/31/17 and 8/31/17 in columns I, L, and O, I still need that in column F so that no months are skipped in column F.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

2/28/17

2/28/17

9/30/17

1/31/18

3/31/17

3/31/17

10/31/17

2/28/18

4/30/17

4/30/17

11/30/17

3/31/18

5/31/17

5/31/17

12/31/17

6/30/17

6/30/17

1/31/18

7/31/17

2/28/18

8/31/17

3/31/18

9/30/17

4/30/18

10/31/17

11/30/17

12/31/17

1/31/18

2/28/18

3/31/18

4/30/18

<tbody>
</tbody>

 

[JoeMo]

For the layout you show in your post, this will list dates starting in F2.

Sub ListDates()
Dim last, first, mnths
last = Application.Max(Range("I:T"))
first = Application.Min(Range("I:T"))
mnths = DateDiff("m", first, last)
Application.ScreenUpdating = False
[F2] = first
With Range("F3:F" & mnths + Range("F2").Row)
    .Formula = "=EOMONTH(F2,1)"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[macroos]

This works perfectly, but when I have formulas/numbers in the columns not containing dates, there seems to be issues.
How can this be resolved?

 

[JoeMo]

Best to tell us in your initial post that columns shown as empty may in fact hold formulas/numbers ....

Here's a fix for that:

Sub ListDates()
Dim last, first, mnths, Rng As Range
Set Rng = Range("I:I,K:K,N:N,Q:Q,T:T")
last = Application.Max(Rng)
first = Application.Min(Rng)
mnths = DateDiff("m", first, last)
Application.ScreenUpdating = False
[F2] = first
With Range("F3:F" & mnths + Range("F2").Row)
    .Formula = "=EOMONTH(F2,1)"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

 

[macroos]

Thank you for your help!

 

[macroos]

On this line: Set Rng = Range("I:I,K:K,N:N,Q:Q,T:T")
Is there a formula for it to look at every third column starting on Column I instead of listing out all the columns?

 

[JoeMo]

On this line: Set Rng = Range("I:I,K:K,N:N,Q:Q,T:T")
Is there a formula for it to look at every third column starting on Column I instead of listing out all the columns?

No formula occurs to me at the moment. Here's some code that will generate the list. In this example it creates a range beginning with col I (col number 9) and adds every third column thereafter through col AA (col num 27). Change the start and end col numbers to suit as well as the step.

Function ColumnNumberToLetter(ColumnNumber As Long) As Variant
    If ColumnNumber > 16384 Then
        MsgBox "There are only 16,384 columns in Excel 2007 and later versions"
        ColumnNumberToLetter = CVErr(xlErrNA)
        Exit Function
    End If
    ColumnNumberToLetter = Split(Columns(ColumnNumber).Cells(1).Address(1, 1), "$")(1)
End Function
Sub GenerateColumnLetters()
'assume start at col I and every 3rd column until col 27
Dim ColInput As String, i As Long
For i = 9 To 27 Step 3
    x = ColumnNumberToLetter(i)
    ColInput = ColInput & "," & x & ":" & x
Next i
Set Rng = Range(Mid(ColInput, 2, Len(ColInput)))
Rng.Select
End Sub

 

[macroos]

Thanks!

 

[JoeMo]

Thanks!

You are welcome - thanks for the reply.