CODEWITHSUNDEEP
vbaexcel
smushi
smushi

Reputation: 719

Excel Macro not finding next empty row, just overwriting last one

I have a excel spreadsheet with macro's which enters details from form to next empty row. For some reason today it is just over writing the last empty row.

I cant figure out why. Here is snippet of code.

'1. Find first empty row in appropriate sheet
        If optActivity = True Then
            Activity.Activate
            RowNum = WorksheetFunction.CountA(Range("Activity_Clubs")) + 1
        ElseIf optEMP = True Then
            EMP.Activate
            RowNum = WorksheetFunction.CountA(Range("EMP_Clubs")) + 1
    End If

Them EMP spreadsheet is working fine, just not the activity one. Anyone have idea, could the activity one be at its max?

Answer: This has seems to solve the problem changing this line

RowNum = WorksheetFunction.CountA(Range("Activity_Clubs")) + 1

to this

RowNum = WorksheetFunction.CountA(Range("Activity_Clubs")) + 2

first line worked all year, until today. Really strange.

Upvotes: 0

Views: 2779

Answers (1)

user2140261
user2140261

Reputation: 7993

To get the last Empty Row in a Column one of the best methods would be to use the following:

Range("A" & Rows.Count).End(xlUp)

For your use try this

'1. Find first empty row in appropriate sheet
        If optActivity = True Then
            RowNum = Activity.Range("A" & Rows.Count).End(xlUp).Row + 1
        ElseIf optEMP = True Then
            RowNum = EMP.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If

Upvotes: 2

Related Questions