none
isnert and fill RRS feed

  • Question

  • Hi

    I have a range of data on a sheet with 20 columns and a variable set of rows.

    I want to:

    • Insert a blank column after column A
    • Populate the blank column (i.e. the new column B) from row 2 to the number of rows in the sheet with the word "Defence" 

    I have tried a couple of different ways (using autofill) and can't quite get it to work.

    Any help you can provide would be much appreciated.

    many thanks,

    Peter 

    Wednesday, January 25, 2017 2:43 PM

Answers

  • Try this macro:

    Sub InsertCol()
        Dim m As Long
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.ScreenUpdating = False
        Range("B1").EntireColumn.Insert
        Range("B2:B" & m).Value = "Defense"
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Wednesday, January 25, 2017 3:47 PM
    Wednesday, January 25, 2017 3:08 PM

All replies

  • Try this macro:

    Sub InsertCol()
        Dim m As Long
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.ScreenUpdating = False
        Range("B1").EntireColumn.Insert
        Range("B2:B" & m).Value = "Defense"
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Wednesday, January 25, 2017 3:47 PM
    Wednesday, January 25, 2017 3:08 PM
  • Hi Hans

    Thanks - that works very well!

    I assume that the Cells.Find statement is looking for the last row with data searching 'backwards' (ie. by virtue of the xlPrevious statement?)

    many thanks

    kinds regards,

    Peter

    Wednesday, January 25, 2017 3:47 PM
  • Yes, that is correct!

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 25, 2017 9:04 PM
  • Hans

    many thanks for this and your other suggestions that have helped me.

    kind regards,

    Peter

    Thursday, January 26, 2017 8:08 AM