Microsoft Example not working (?)

[rupana]

Hello sir(s)

I was looking for a VBA code for choosing the first empty cell in a column containing some data and I came across the following code on one of the microsoft webpages.

[ https://msdn.microsoft.com/en-us/library/office/hh211481(v=office.14).aspx]

Sub NewRecord()
' Add new record and increment the value in the first column.
Sheets("Employees").Activate
' Select the first cell after the last filled cell in column A.
Range("A1").End(xlDown).Offset(1, 0).Select
' Determine the maximum value in the column and add 1.
ActiveCell = WorksheetFunction.Max(ActiveCell.EntireColumn) + 1
End Sub

I copied it and when I ran the code, I got an error - error message uploaded as jpeg file.

Can I know why this is code is wrong ? and if wrong, how to correct it?

Thanking you in advance.
With regards
Ravi Shankar Rupanagudi
Hyderabad

 

[Chihiro]

Hi,

The code does run. But won't do what is described.
The error is likely caused by not having sheet named "Employee" in your workbook.

Code will change depending on if you want to find first empty row between data or first empty row after last row with data.

Below will search for first empty row in Col A, including A1 and select the cell.

Sub NewRecord()
Dim i As Long, r As Range, lastRow As Long

' Activates "Employees" sheet
Sheets("Employees").Activate
' Find last Row containing data in Col A
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' Search for first empty row in Col A using lastRow
For i = 1 To lastRow
    Set r = Range("A" & i)
    If Len(r.Value) = 0 Then
        ActiveSheet.Cells(i, "A").Select
    End If
Exit For
End Sub

 

[rupana]

Sir / Madam,

Thanks for your immediate reply. I have renamed the sheet1 as Employees before I ran the code/macro.
Basically I wanted to [ " Select the first cell after the last filled cell in column A. " ], as given in the example in one of my uses. I am using Excel 2010 and not 2014. I think that may be the issue, as the webpage of microsoft mentions 2014 in the webpage address { v=2014}. So I feel that may be the issue, unless I am wrong.

As I had attached the error screen shot as a Jpeg, it clearly shows the message that the code cannot be run. May be Chihiro is using 2014 version !?

Thanks and regards for all the efforts the members have put it.

Regards
Ravi Shankar Rupanagudi
Hyderabad

 

[Chihiro]

Hi,

Code is good for 2010 as well.

To select first cell after last filled column (ignoring blank cells in between).

Sub NewRecord()
Dim lastRow As Long

' Activates "Employees" sheet
Sheets("Employees").Activate
' Find last Row containing data in Col A
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' Select cell immediately after lastRow
ActiveSheet.Cells(lastRow + 1, "A").Select
End Sub