How to create Macros buttons to add or delete columns and rows.

Hi all,

So I'm trying my hand at creating VBA buttons and functions that do the following.

Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)

Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.

Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.

Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)

Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.

Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.

I'm "learning" VBA coding by trial and error.... mostly frustrating errors... sigh... any help would be good. i have included a sample file that I would like work on.

In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns.
There are eight rows with data and the ninth row is the empty buffer row.

The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.

I hope I'm clear .... =(

Thanks.
Damien

Damienchew,

Is there a purpose for the buffer column? It may be easier to have it removed altogether, then if you wanted to add more information you could just click on the "Add Row" or "Add Column" buttons as required?

Mowgli

Hi Mowgli,

Actually there is no purpose for the buffer row or column. I just thought it would be easier to locate an empty row/column, because there is still another column/row that calculates averages.
But from reading the forum and other sources I realise I could just locate the last used row/column then do an offset, then do an insert... So far I have not been able to do something like that.... I'm totally new at coding... so all errors... =(

Originally Posted by Mowgli2

Damienchew,

Is there a purpose for the buffer column? It may be easier to have it removed altogether, then if you wanted to add more information you could just click on the "Add Row" or "Add Column" buttons as required?

Mowgli