Call a procedure to get the first empty column number – Excel VBA

You have a spreadsheet and you want to add some data in the first empty column. How do you know which column to use? You simply use your eyes and look. Guide your mouse to move the cursor to a cell in the empty column, enter a formula, then autofill down. But how does one automate this for a spreadsheet when you do not know beforehand which column is empty (eg column B, column AB, etc)? Your computer does not have eyes to simply check.

Or does it?

A webcam is an eye, but your webcam will not assist you with automation. It will assist you with a fine evening on Chatroulette.

Below is code to determine the location of the first empty column in a spreadsheet. As a bonus, it includes code that will determine the last row where data exists on the spreadsheet [1].

Take the following sheet which holds episode details of Breaking Bad season 1 [2].

After running the code, it returns the next empty column (emptyCol) as column 5 (column E), and the last row with data as row 8. 

Now I want to populate column 5 (column E) with data. Using the Original air date, I want to list the year. I can use something like this:

Range(Cells(2, emptyCol), Cells(rowLast, emptyCol)).Formula = "=Year(Originalairdate)"

Where emptyCol = 5, rowLast = 8. The “Range(Cells(), Cells())” part are the coordinates of the range where I wish to populate cells: from rows 2 to 8, restricted to column 5. I have used the year formula on the data in the “Original air date” column [3]. 

Next scenario. What happens when you have to repeat this action? I now want to have a column with the month of the original air date (using the Month() formula), and then populate the next empty column with the day (using the Day() formula). This would be populated in column F and G respectively. Before populating data in the next empty column, the software needs to know where the empty column is. Like this:

• Find the column number of the next empty column
• Remember the column number
• Point to empty column using the remembered column number
• Populate with data (eg year)
• Find the column number of the next empty column
• Remember the column number
• Point to empty column using the remembered column number
• Populate with data (eg month)
• Find the column number of the next empty column
• Remember the column number
• Point to empty column using the remembered column number
• Populate with data (eg day).

Each time a column is populated with data, the empty column number changes by one (year in column 5, month in column 6, day in column 7). You could copy and paste the whole code each time to get the next empty column (and last row with data if this changes). Or, you could set the code aside in a procedure. When you need to run the code, call the procedure. Here I have placed the code in a procedure called "EmptyCol_LastRow". 

I can call this procedure (EmptyCol_LastRow) as required to get the new emptyCol values, as follows:

• Call procedure to get emptyCol and lastRow
• emptyCol = 8, lastRow = 8
• Populate with year
• In column 8 
• Call procedure to get emptyCol and lastRow
• emptyCol = 9, lastRow = 8
• Populate with month
• In column 9
• Call procedure to get emptyCol and lastRow
• emptyCol = 10, lastRow = 8
• Populate with day
• In column 10.

The corresponding code that populates the columns with data is shown below.

Rather than copy paste lines of code to get updated column and row numbers, simply call the procedure as required!

References and notes

1. Code modified from ozgrid.com VBA/ExcelRanges page. 

2. Data from Breaking Bad Episodes Wiki page.

3. I manually created a named range for the Original air date column called “Originalairdate”.