Insert Blank Column Code Error

B-radK

Board Regular
Joined
Apr 1, 2010
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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,
Brad.

Screenshot 2020-12-17 122843.jpg


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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
625
Office Version
  1. 365
Platform
  1. Windows
Hi, Brad.
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
Joined
Dec 12, 2019
Messages
33
Office Version
  1. 2010
Platform
  1. Windows
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
 

B-radK

Board Regular
Joined
Apr 1, 2010
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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,
Brad.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,046
Members
415,875
Latest member
Tarali

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top