Effficient Way to Insert columns, renaming Column Headers and using Len to count the cells

[bearcub]

I have this macro that I just recorded using the macro recorder and it is way too long and messy. I would like a more efficient way of how to do this (so I could write something like this on my own).

I have 6 columns in a report where i want to insert a blank column, rename each one of them and then add the Len function to the column to the left of it to count the number of characters.

The range of columns that will be inserted is from Column K:R. After all the columns have been inserted the new range extends from K:Z (including the 8 additional columns)

The first column that will be inserted will be in column K (note that the Len function points to K2).


Here is a snippet of the code I got from the macro recorder:

Sub InsertColumn_PhoneCount()
'
' InsertColumn_PhoneCount Macro
'
On Error Resume Next
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight
    Range("Table1[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Primary Count"
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight
    Range("Table1[[#Headers],[Phone Count]]").Select
    ActiveCell.FormulaR1C1 = "Home Count"
    Selection.Copy
    Range("Table1[[#Headers],[Column1]]").Select
    ActiveSheet.Paste
    Columns("N:N").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("P:P").Select
    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=LEN(K2)"
    Range("L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("N2").Select
    ActiveSheet.Paste
    Range("P2").Select
    ActiveSheet.Paste
    Range("R2").Select
    ActiveSheet.Paste
    Range("T2").Select
    ActiveSheet.Paste
    Range("V2").Select
    ActiveSheet.Paste
    Range("X2").Select
    ActiveSheet.Paste
    Range("Z2").Select
    ActiveSheet.Paste

I was thinking that i could use a For Next statement to add the columns but I'm not sure how to go about doing this.

I have the sheet as an Excel Table so the formulas will fill down automatically (there are presently over 67,000 rows on the spreadsheet).

Thank you for your help in Advance,

Michael

 

[pbornemeier]

I made an error in my last post's last line

will vary the value of lColIndex from 8 to 2
So that this line will start inserting columns to the left of Column 8,7,6,5...2 of the table

This code

   lFirstColumn = 1  
    lLastColumn = 8 
    lColIndex = lLastColumn + IIf(lTableColumnCount > lLastColumn, 1, 0) To lFirstColumn + 1 Step -1
                   =          8                                          To         1    + 1

I also had an error in the code I used to insert the formula into the inserted columns. Here is the update:

Option Explicit

Sub ExpandTable()
    'https://www.mrexcel.com/forum/excel-questions/1044730-effficient-way-insert-columns-renaming-column-headers-using-len-count-cells.html
    
    'Insert columns after specified columns in the first listobject (table) on the active worksheet
    'Update values for lFirstColumn & lLastColumn below
    'Add header to each inserted column that consists of its left cell value + " Count"
    'Add LEN for cell left of each inserted column in 2nd and subsequent rows
    
    Dim tbl As ListObject
    Dim lColIndex As Long
    Dim lFirstColumn As Long
    Dim lLastColumn As Long
    Dim lTableColumnCount As Long
    
    lFirstColumn = [COLOR="#FF0000"]11  [/COLOR]'The first column in the table that will have a column inserted after it
    lLastColumn = [COLOR="#FF0000"]18   [/COLOR]'The last column in the table that will have a column inserted after it
                                    '  if this is greater than the total number of columns in the table
                                    '  the last table column will be used.
    
    Set tbl = ActiveSheet.ListObjects(1)
    
    lTableColumnCount = tbl.Range.Columns.Count
    If lLastColumn > lTableColumnCount Then lLastColumn = lTableColumnCount
    
    'Add columns and headers after all but last column
    For lColIndex = lLastColumn + IIf(lTableColumnCount > lLastColumn, 1, 0) To lFirstColumn + 1 Step -1
        tbl.Range.Columns(lColIndex).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        tbl.Range.Cells(1, lColIndex).Value = tbl.Range.Cells(1, lColIndex - 1).Value & " Count"
    Next

    If lLastColumn = lTableColumnCount Then
        'Add column and header after rightmost table column
        tbl.Resize Union(tbl.Range, tbl.Range.Offset(0, 1))
        lColIndex = tbl.Range.Columns.Count
        tbl.Range.Cells(1, lColIndex).Value = tbl.Range.Cells(1, lColIndex - 1).Value & " Count"
    End If
    
    'Add LEN Formula to inserted columns
    With tbl.Range.Cells(2, lFirstColumn + 1)
        .FormulaR1C1 = "=LEN(RC[-1])"
        .Copy
    End With
    For lColIndex = lFirstColumn + 1 To[COLOR="#FF0000"] lFirstColumn + [/COLOR]2 * (lLastColumn - lFirstColumn + 1) Step 2
        tbl.Range.Cells(2, lColIndex).Select
        ActiveSheet.Paste
    Next
    
End Sub

If your table starts in column A then using
lFirstColumn = 11
lLastColumn = 18

Would add columns & formula after K:R

 

[bearcub]

Fabulous - works like a charm. I'm going spend some time to work at the 2 different versions and try to understand why it works. the more I get into it i realize that VBA can do a lot more things than you can on the spreadsheet itself though that can be amazing as well.

5 Star, thank you for your help and patience.

Michael

 

[pbornemeier]

Glad to help. Ask if you need anything clarified.

 

[bearcub]

Thank you for the offer.

I am trying to understand how the code works. I have a good grasp of VBA so I do understand how it works but a lot of times i get confused in interpreting what it is doing.. I think it is just a mater of translating the terminology into English. Right now, it looks like Latin to me and I have to fight my way trying to make mental connections to what is happening.

I understand what set, Resize, union, count etc mean but in some instances I don't know why it is being used where it is. For example, if you want to go down to the last cell in a range you would use the rows. count (xldown) property to emulate the Control Down arrow, correct. I just need something to compare the terminology to so I can interpret what it is doing.

If this is too much to ask, I understand. But, it would help get a deeper understanding of how the terminology and logic is applied in this instance. Now elaborate, just a notes here and there will help make it clear.

Thank you for your time and help.

Michael

 

[pbornemeier]

There are multiple ways using VBA to get the last populated row in a column. Here are five:

?Range("A1").End(xlDown).Row  '1
?Activesheet.UsedRange.Rows.Count  '2
?Cells(Rows.Count, 1).End(xlUp).Row  '3
?Columns("A:A").Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Row  '4
?Application.WorksheetFunction.CountA (Range("A:A"))  '5

Populate column A with some data

Paste this code in the immediate window and see what answers you get for each by pressing return after each number.

Can you think of different circumstances that will cause each to fail?

Sparsely column A with data & repeat

AFAIK one of them will always work.

I like #3 even though it will fail under one circumstance.