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

Posted on March 22, 2014 by

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:

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:

Sub DeleteAll()
   Dim WSOut As Worksheet
   Dim intAllRows As Double
   Dim intAllCols As Double

   Set WSOut = Worksheets("Output")

    intAllRows = Cells.Rows.Count
    intAllCols = Cells.Columns.Count

    With WSOut
        .Range(.Cells(2, 1), .Cells(intAllRows, intAllCols)).ClearContents
    End With
End Sub

Sub DeleteAll()

Dim WSOut As Worksheet

Dim intAllRows As Double

Dim intAllCols As Double

Set WSOut = Worksheets("Output")

intAllRows = Cells.Rows.Count

intAllCols = Cells.Columns.Count

With WSOut

.Range(.Cells(2, 1), .Cells(intAllRows, intAllCols)).ClearContents

End With

End Sub

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:

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:

Sub lastValue()

Dim a As Double
Dim b As Double
Dim c As Double
Dim d As Double

a = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Rows
b = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

c = ActiveSheet.Cells(b, Columns.Count).End(xlToLeft).Columns
d = ActiveSheet.Cells(b, Columns.Count).End(xlToLeft).Column

Debug.Print a; b; c; d

End Sub

Sub lastValue()

Dim a As Double

Dim b As Double

Dim c As Double

Dim d As Double

a = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Rows

b = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

c = ActiveSheet.Cells(b, Columns.Count).End(xlToLeft).Columns

d = ActiveSheet.Cells(b, Columns.Count).End(xlToLeft).Column

Debug.Print a; b; c; d

End Sub

Pretty much that is all! Enjoy the code responsibly 😀

Tagged with: Excel, last cell in column, last cell in row, last cell value, last used cell, VBA

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

Related posts: