VBA – Defining last cells in row/column and size of spreadsheet

In this post I will simply show a few VBA tricks, for defining last used cell in a row (or column) and defining the size of the worksheet.

Let’s assume that you wish to delete all the contents from a spreadsheet, without its first row. There is an option to hard-code it, but it will not work well with all versions in Excel. That is why, you could use the property “Count” to obtain the number of cells. It looks like this:
RowsAndCols

You see, that you obtain immediately information for the number of rows and columns in the spreadsheet. Pretty workable.

That is how the code looks like, if you want to use it:

The second piece of code is more challenging. Anyone dealing with Excel has experienced the need to find what is the last value in a row/column and which row/column consists it. Plenty of people simply record a macro and use CTRL + Arrows, in order to come up with something. However, there is another way – to try to program it. Here is how it looks:

C4

In this example we have put the value “100” in the cell C4 (column 3, row 4) and we have determined which is the last used row in column C (the answer is row 4) and which is the last used column in the forth row – the answer is row 3. Their values are also determined, as you see in the immediate window. How we have done this miracle? We have simple asked VBA to give us the first value it meets from the end of the column to the top and from the end of the row to the left! As you have noticed in the previous example, the Columns.Count gives the number of the columns.

Here comes the code:

Pretty much that is all! Enjoy the code responsibly 😀

About

VBA Developer

Tagged with: , , , , ,