I would like to insert separating columns into an Excel report to make the existing columns easier to view.
The report is created dynamically and I never know how many columns there will be; there could be 5, 10, 17, etc.
The section starts at F and goes to ival=Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")
So if ival=10
then the columns are F G H I J K L M N O, and I need to insert columns between F&G, G&H, H&I, I&J, ... and N&O.
This may be a possibility for inserting columns: Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i).Insert
But I'm not sure how to loop through ival
.
Sub InsertColumns()
Dim iVal As Integer
Dim Rng As range
Dim LastRow As Long
Dim i As Integer
With Sheets("sheet1")
LastRow = .range("D" & .Rows.Count).End(xlUp).Row
End With
iVal = Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")
For i = 7 To iVal - 1
Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i+1).Insert
Next i
End Sub
INDEX formula to make a dynamic named range in Excel On the right side, you use the INDEX(array, row_num, [column_num]) function to figure out the ending reference. Here, you supply the entire column A for the array and use COUNTA to get the row number (i.e. the number of non-entry cells in column A).
Insert a New Column (Keyboard Shortcut) Select a cell in the column to the left of which you want to add a new column. Use the keyboard shortcut Control Shift + In the Insert dialog box that opens, click the Entire Column option (or hit the C key) Click OK (or hit the Enter key).
The below code should work without needing to worry about ival
:
Sub InsertSeparatorColumns()
Dim lastCol As Long
With Sheets("sheet1")
lastCol = Cells(2, .Columns.Count).End(xlToLeft).Column
For i = lastCol To 7 Step -1
.Columns(i).Insert
.Columns(i).ColumnWidth = 0.5
Next
End With
End Sub
Try this:
Sub InsertSeparatorColumns()
Dim ws as Worksheet
Dim firstCol As String
Dim lastRow As Long
Dim i As Long
Dim howManySeparators As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
firstCol = "F"
lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
howManySeparators = Application.WorksheetFunction.CountIf _
(ws.range("D2:D" & LastRow), "Other")
For i = 1 To howManySeparators * 2 Step 2
ws.Range(firstCol & 1).Offset(, i).EntireColumn.Insert
Next i
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With