Find last used cell in a row

Definition

By using VBA coding you can find the last used a row and copy that information somewhere else.

Example

We will explain a simple example by doing this with 10 rows. Here we will search for the last cell in a row and copy into a new column.. Download the following file for the example:

Make sure your macros are enables, otherwise it will not work.

The following screen will be shown:

We want the last cell in column N

The code

Sub lastcell()

a = 1

Do While a < Range("o1")

Range("m" & a).Select
ActiveCell.FormulaR1C1 = "=COUNTA(RC[-12]:RC[-1])"
Range("a" & a).Select
mycell = Range("m" & a) - 1
ActiveCell.Offset(0, mycell).Select
ActiveCell.Copy
Range("n" & a).Select
ActiveCell.PasteSpecial
a = a + 1

Loop

End Sub

Using more rows

You can manipulate the code, to add more rows. If you cannot do this, contact us, and we can help you.