0

I am trying to add multiple columns at the end of the existing data set and add multiple columns. Below is the code that I have working right now:-

Sub AddColumn()

Range("A1").Select
Selection.End(xlToRight).Select

ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "RFQ 1"

ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "RFQ 2"

ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "RFQ 3"

End Sub

However, the first issue is the code looks like a patch up job and I am not able to find any better solutions to this. Second is,if there is any accidental blank space in column headers then the whole thing might end up messing the existing data set. So, could you please provide a better workable solution to this. (Sorry, I am extremely new to the VBA environment).

BigBen
  • 46,229
  • 7
  • 24
  • 40
E L
  • 1
  • 1
  • 5

1 Answers1

0

Just a one-liner using End(xlToLeft) to get the last column and Offset and Resize to refer to the next 3 cells:

Sub AddColumn()
    Cells(1, Columns.Count).End(xlToLeft).Offset(,1).Resize(,3).Value = Array("RFQ 1", "RFQ 2", "RFQ 3")
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Hello BigBen. Thank you sooooo much... the code works like a charm. Thank you. – E L Aug 26 '21 at 18:28
  • Hello BigBen, Thanks for the huge help that you have provided. However, I am stuck in a new problem now. So, when I am manually adding the columns in excel the macro is running as it is but whenever the columns are added by the macro itself, it is showing error 1004 when I am trying to put filter on that newly added column via the macro. The error code Goes "Run-time error '1004' AutoFilter method of Range class failed". Could you please help me out here. Thanks. – E L Sep 12 '21 at 20:38
  • @EL - can you ask a new question please? – BigBen Sep 12 '21 at 20:58
  • Sure.. Thanks.. I'll provide more details. – E L Sep 13 '21 at 07:27