Add and delete columns in VBA

[nodochau]

Hello all,
I am trying to use VBA to add and delete columns in my spreadsheet. The VBA just works for adding. Deleting not working at all.
The spreadsheet template is designed with 8 samples and sample number one starts at column 6. The goal is:
If job has more than 8 samples then macro will add more columns and if less then macro will delete columns.

Here is my code:

Code: Select all

Let>work_file_link=C:\Users\aaa\Documents\QSF-Template\QSF-FI-2-EDITED.xlsx
Let>new_file=C:\Users\aaa\Documents\QSF-Template\test.xlsx
Let>samples=6
Let>column=14
Gosub>edit_columns
SRT>edit_columns
  Let>link=work_file_link
  Let>save_link=new_file
  Let>j=samples
  Let>x=column
  VBStart
    Dim i, start_col, end_col, number_of_samples
    Dim link

    Sub Insert_col(link, j, save_link, x)
       number_of_samples = x - 6
      Const xlToRight = -4161
      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = False
      Set objWB = objExcel.Workbooks.Open(link)
      Set objSheet = objwb.Sheets("ProcessSheet")
      If j > number_of_samples Then
          j = j + 5
          start_col = x
        For i = start_col to j
          objSheet.Columns(i).Insert xlToRight
        Next
      Else
        start_col = 6 + j
        end_col = number_of_samples - j
        For i =  1 To end_col
          objSheet.Columns(start_col).Delete xlToRight
        Next
      End if
     
      objwb.SaveAs  (save_link)
      objWB.Close True
      objExcel.Quit
    End Sub
  VBEND

  VBRun>Insert_col,link,j,save_link,x
  
END>edit_columns

[Dorian (MJT support)]

I'd probably need to see your sheet to understand it, but to delete the columns you can use XLDelCol. This is a proof of concept that you can hopefully apply/adapt.

Code: Select all

Let>Col_List=A^B^C^D^E^F^G^H^I^J^K^L^M^N^O^P^Q^R^S^T^U^V^W^X^Y^Z
Separate>Col_List,^,Col_Array

XlOpen>d:\test.xlsx,1,xlBook
XLGetSheetDims>xlBook,Sheet2,Rows,Cols

//Delete any cols after Col 10 (J)
While>Cols>10
  Let>Last_Col=Col_Array_%Cols%
  XLDelCol>xlBook,Sheet2,%Last_Col%:%Last_Col%
  Let>Cols=Cols-1
EndWhile

And to add a column we can use VBA. Here's the basis of it to get you started. It adds a column after column M, and could be used with variables/loops as desired :

Code: Select all

XlOpen>d:\test.xlsx,1,xlBook
LabeltoVar>InsertCol,VBAcode
XLRunCode>xlBook,VBAcode

/*
InsertCol:
    Sheets("Sheet2").Select
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
*/

I'll leave you to figure out the looping and implementation, as I think the above parts are the only aspects you need help with.

This post is well worth reading to help get your vba code into Macro Scheduler.

[Awe3423]

Can you please share your spreadsheet with us, so that we can help with almost accuracy? I am also curious about this, I am new to learning VBA.

[Dorian (MJT support)]

Can you please share your spreadsheet with us, so that we can help with almost accuracy? I am also curious about this, I am new to learning VBA.

My one? It would work on any "test.xlsx" workbook that had a Sheet2. Or of course the script can be edited to work on a workbook of any name and any sheetname. I'd just used a generic spreadsheet to test.

[nodochau]

Can you please share your spreadsheet with us, so that we can help with almost accuracy? I am also curious about this, I am new to learning VBA.

Insert Column:

Code: Select all

VBStart
    Dim i, start_col, end_col, y
    Dim link
    Sub Insert_col(link, j, save_link, x)
       y = x - 6
      Const xlToRight = -4161
      Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = False
      Set objWB = objExcel.Workbooks.Open(link)
      Set objSheet = objwb.Sheets("ProcessSheet")
      If j > y Then
          j = j + 5
          start_col = x
        For i = start_col to j
          objSheet.Columns(i).Insert xlToRight
        Next
      End if
      objwb.SaveAs  (save_link)
      objWB.Close True
      objExcel.Quit
    End Sub
  VBEND

  VBRun>Insert_col,link,j,save_link,x

Delete Cols you can use Dorian's sample