# Delete column and insert column n formula automatically

1. ## Delete column and insert column n formula automatically

Hi All Excel master,
I have file excel AB10 it contain raw data need to be edit and put in new workbook AB. Raw data contain a few unused column so i need to delete it and then i also need to insert some columns with excel formula. Here i Attach the file,
1. i need to delete P column.
2. i need to insert 2 column after N column with formula in O column = (N10-O\$8)/30 and P column =IF(O10<=1,"(0 - 1 bulan)",IF(O10<=3,"(>1 - 3 bulan)",IF(O10<=6,"(>3 - 6 bulan)",IF(O10<=12,"(>6 - 12 bulan)",IF(O10<=24,"(>1 - 2 tahun)",IF(O10<=36,"(>2 - 3 tahun)",IF(O10<=48,"(>3 - 4 tahun)",IF(O10<=60,"(>4 - 5 tahun)"))))))))
please kindly help me to solve this problem using macro. Thanks in advance  Register To Reply

2. ## Re: Delete column and insert column n formula automatically

Try this:

HTML Code:
``````Sub zz()
Columns("P").Delete
Columns("o:p").Insert
Cells(10, "o").Formula = "=(N10-O\$8)/30"
Cells(10, "p").Formula = "=IF(O10<=1,""(0 - 1 bulan)"",IF(O10<=3,""(>1 - 3 bulan)"",IF(O10<=6,""(>3 - 6 bulan)"",IF(O10<=12,""(>6 - 12 bulan)"",IF(O10<=24,""(>1 - 2 tahun)"",IF(O10<=36,""(>2 - 3 tahun)"",IF(O10<=48,""(>3 - 4 tahun)"",IF(O10<=60,""(>4 - 5 tahun)""))))))))"
End Sub``````
or

HTML Code:
``````Sub zz()
Columns("P").Delete
Columns("o:p").Insert
Cells(10, "o").Formula = "=(N10-O\$8)/30"
Cells(10, "p").Formula = "=LOOKUP(O10,{1,3,6,12,24,36,48,60},{""(0 - 1 Bulan)"",""(>1 - 3 Bulan)"",""(>3 - 6 Bulan)"",""(>6 - 12 Bulan)"",""(>1 - 2 Tahun)"",""(>2 - 3 Tahun)"",""(>3 - 4 Tahun)"",""(>4 - 5 Tahun)""})"
End Sub``````  Register To Reply

3. Originally Posted by ikboy Try this:

HTML Code:
``````Sub zz()
Columns("P").Delete
Columns("o:p").Insert
Cells(10, "o").Formula = "=(N10-O\$8)/30"
Cells(10, "p").Formula = "=IF(O10<=1,""(0 - 1 bulan)"",IF(O10<=3,""(>1 - 3 bulan)"",IF(O10<=6,""(>3 - 6 bulan)"",IF(O10<=12,""(>6 - 12 bulan)"",IF(O10<=24,""(>1 - 2 tahun)"",IF(O10<=36,""(>2 - 3 tahun)"",IF(O10<=48,""(>3 - 4 tahun)"",IF(O10<=60,""(>4 - 5 tahun)""))))))))"
End Sub``````
or

HTML Code:
``````Sub zz()
Columns("P").Delete
Columns("o:p").Insert
Cells(10, "o").Formula = "=(N10-O\$8)/30"
Cells(10, "p").Formula = "=LOOKUP(O10,{1,3,6,12,24,36,48,60},{""(0 - 1 Bulan)"",""(>1 - 3 Bulan)"",""(>3 - 6 Bulan)"",""(>6 - 12 Bulan)"",""(>1 - 2 Tahun)"",""(>2 - 3 Tahun)"",""(>3 - 4 Tahun)"",""(>4 - 5 Tahun)""})"
End Sub``````
hey thanks for the code,i havent try it yet but 1 more question i want to make every cell in o and p filled until they found "total row" as seen in excel file,do you knpw the code as well? thanks b4  Register To Reply

4. ## Re: Delete column and insert column n formula automatically

Try this:

HTML Code:
``````Sub zz()
Columns("P").Delete
Columns("o:p").Insert
Cells(10, "o").Formula = "=(N10-O\$8)/30"
Cells(10, "p").Formula = "=LOOKUP(O10,{1,3,6,12,24,36,48,60},{""(0 - 1 Bulan)"",""(>1 - 3 Bulan)"",""(>3 - 6 Bulan)"",""(>6 - 12 Bulan)"",""(>1 - 2 Tahun)"",""(>2 - 3 Tahun)"",""(>3 - 4 Tahun)"",""(>4 - 5 Tahun)""})"
Range(Cells(10, "o"), Cells(Cells(10, "n").End(4).Row, "p")).FillDown
End Sub``````  Register To Reply