Hello Team,
I have a sheet with 3 months' worth of information (starting with the last day of the previous month). The dates run across row 1. The column count can vary, depending on the months displayed. I.e. April | May | June = a total of 91, whereas May | June | July would have a total of 92. Obviously if Feb was included, it would be less. See pic below.
I need to insert a column, every second column. I can do this running from left to right with the code below.
However, I thought it would be much 'cleaner' if I started from right to left. Because, if I started from left to right, I would have to change the code every time I ran over a new month and the total number of days were greater/less.
I'm fairly new to vba and I'm trudging my way through an online vba course (Leila Gharani - other recommendations would be helpful too, thanks) and I gave this one a shot, but no joy.
I would need the last blank column to be column E.
Any ideas on how to run this?
Also, I have this code which would work after I inserted the blank columns and it runs at 0.98 seconds, which I thought was pretty slow for only a few lines of code. Where I run the calculation between the original columns seems to be slow. I was wondering if my code could be simplified (being new to vba, I'm sure it can be).
Thanks team. I'd appreciate any assistance I can get.
Brad.
I have a sheet with 3 months' worth of information (starting with the last day of the previous month). The dates run across row 1. The column count can vary, depending on the months displayed. I.e. April | May | June = a total of 91, whereas May | June | July would have a total of 92. Obviously if Feb was included, it would be less. See pic below.
I need to insert a column, every second column. I can do this running from left to right with the code below.
VBA Code:
Dim i As Long
For i = 5 To 200 Step 2
Columns(i).Insert
Next
However, I thought it would be much 'cleaner' if I started from right to left. Because, if I started from left to right, I would have to change the code every time I ran over a new month and the total number of days were greater/less.
I'm fairly new to vba and I'm trudging my way through an online vba course (Leila Gharani - other recommendations would be helpful too, thanks) and I gave this one a shot, but no joy.
I would need the last blank column to be column E.
VBA Code:
Dim i As Long
Dim LastCol As Range
Set LastCol = Range("A" & Columns.Count).End(xlToLeft).Columns
For i = LastCol To 5 Step -2
Columns(i).Insert
Next
Any ideas on how to run this?
Also, I have this code which would work after I inserted the blank columns and it runs at 0.98 seconds, which I thought was pretty slow for only a few lines of code. Where I run the calculation between the original columns seems to be slow. I was wondering if my code could be simplified (being new to vba, I'm sure it can be).
VBA Code:
Sub Insert_Cols_Reverse()
Application.ScreenUpdating = False
'The code for inserting blank columns from right to left will be entered here
'Copy blank cells in Row 1 with cell to the right.
With Range("A1:CV1")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
.Value = .Value
End With
'Calculate difference between original columns
Dim c As Range
For Each c In Range("A1").CurrentRegion
If c.Value = "" Then
c.FormulaR1C1 = "=RC[1]-RC[-1]"
c.PasteSpecial (xlPasteValuesAndNumberFormats)
End If
Next c
'Copy and Paste Values of all cells so I can delete the original columns
Cells.copy
Cells.PasteSpecial (xlPasteValuesAndNumberFormats)
Application.CutCopyMode = False
Range("A1").Select
'Delete original columns.
Dim t As Long
For t = 4 To 200 Step 1
Columns(t).Delete
Next
Application.ScreenUpdating = True
End Sub
Thanks team. I'd appreciate any assistance I can get.
Brad.