Add formula to last used column, fill down, delete columns based on named range


Board Regular
Dec 17, 2008
Hi all,

I found the below code which adds a column header next to the last used column:

VBA Code:
Dim colLast As Long

    colLast = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(1, colLast + 1) = "New Column Header"

What I would like to do is to add a formula below this new column header (new column, row 2) and then fill down according to the last row in column A (maybe using Rowcount?)

Then I would like to organise the columns by sorting them in a specific order and deleting columns that are not necessary. Seeing as there are around 30 columns and I only need to see 15, would it be possible to make a list of required headers by using a named range with a specific order and have the code delete those columns that are not on that named range? The column names will always be the same.

I found this code by @Fluff but I could not make it work.

VBA to delete entire column based on column header name

VBA Code:
Sub Nirvehex()
   Dim Ary As Variant
   Dim i As Long
   Dim Fnd As Range
   With Sheets("List")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 0 To UBound(Ary)
      Set Fnd = Range("1:1").Find(Ary(i, 1), , xlValues, xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then Fnd.EntireColumn.Delete
   Next i
End Sub

Any help would be appreciated. Thanks in advance.

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Latest member