# Insert Blank Column Code Error

##### Board Regular
Hi Team,

I have a worksheet where I work with 3 consecutive months of the year. I have code to find the last day of the month, then enter a blank column to separate all of the months. This code below, kindly provided by @Peter_SSs works extremely well. However, when the 3 consecutive months spill over to a new year, i.e. November, December and January, the code does not separate December and January. Similarly with December, January and February, it does not separate them at all. I've tried to add an addition year's worth of months to the end of the "AllHdrs = Split..." line of code, but this doesn't seem to work. Any suggestions?

Thanks,

VBA Code:
``````'Enter a blank column after the end of each month.

Dim AllHdrs As Variant, Hdr As Variant
Dim rFound As Range

AllHdrs = Split("31-Jan|29-Feb|31-Mar|30-Apr|31-May|30-Jun|31-Jul|31-Aug|30-Sep|31-Oct|30-Nov|31-Dec", "|")

For Each Hdr In AllHdrs
Set rFound = Rows(1).Find(What:=DateValue(Hdr), LookIn:=xlFormulas, LookAt:=xlWhole)
If Not rFound Is Nothing Then Columns(rFound.Column + 1).Insert
Next Hdr``````

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Osvaldo Palmeiro

##### Well-known Member
Try this one:
VBA Code:
``````Sub InsertBlankColumns()
Dim dt As Long, LD As Range
dt = 1
Do While Cells(1, dt) <> ""
Set LD = Rows(1).Find(DateSerial(Year(Cells(1, dt)), Month(Cells(1, dt)) + 1, 1) - 1)
If Not LD Is Nothing Then
If LD.Offset(, 1).Value <> "" Then
Columns(LD.Column + 1).Insert
End If
Else: Exit Sub
End If
dt = LD.Column + 2
Loop
End Sub``````

#### maras

##### New Member
Try my solution.
VBA Code:
``````Sub test_maras()
Dim mth As Variant
Dim insC As Range
Dim i As Integer, d As Integer, md As Integer

mth = Application.Transpose(Application.Transpose([a1].CurrentRegion.Rows(1)))
For i = 1 To UBound(mth)
d = Day(mth(i))
md = Day(Application.EoMonth(mth(i), 0))
If d = md Then _
Set insC = Union(IIf(insC Is Nothing, Cells(1, i + 1), insC), Cells(1, i + 1))
Next
insC.EntireColumn.Insert
Set insC = Nothing
End Sub``````

##### Board Regular
Hello @Osvaldo Palmeiro and @maras.

First of all, thank you for your replies and second of all; I'm very sorry for the late reply. I was away for the silly season, so now I'm back to it all.

I'll get onto this right away and get back to you both.

Thanks again,

Replies
1
Views
96
Replies
7
Views
235
Replies
0
Views
83
Replies
2
Views
86
Replies
2
Views
364

1,127,133
Messages
5,622,904
Members
415,938
Latest member
Walkie1994

### 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.

### Which adblocker are you using?

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

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