Insert Blank Column Code Error

B-radK

Board Regular
Joined
Apr 1, 2010
Messages
95
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top