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

Question

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.

` today it is just over writing the last empty row` So the code was working fine, and even though code is NOT changed results ARE chnaged?? So, what makes you think it is an issue with your code? there is a chance that a row was cleared — user2140261, Sep 24 '13 at 01:11
You should try fully qualifying your ranges, e.g., `Activity.Range(...)`, and using a different method to find the last row, e.g., `Activity.Range("A" & Activity.Rows.Count).End(xlUp).Row` — Doug Glancy, Sep 24 '13 at 01:14
I ve done numerous tests, and it just ends up over writing the last line of the excel. — smushi, Sep 24 '13 at 01:14
The same thing you had in it. The important part is that you specify what worksheet the `Range` is in. user2140261's answer is a more complete explanation. — Doug Glancy, Sep 24 '13 at 01:40
Is [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) what you are trying? — Siddharth Rout, Sep 24 '13 at 05:39
Get sure your named ranges (`Activity_Clubs` and `EMP_Clubs`) are big enough to fit all data. Also, that you have no gaps in then. — LS_ᴅᴇᴠ, Sep 24 '13 at 07:33

score 2 · Accepted Answer · answered Sep 24 '13 at 01:34

2

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

answered Sep 24 '13 at 01:34

user2140261

7,855
7
32
45

hey, did that. THen next line i got error on this line a. General - same for every type of grant Range("Processing_Date").Offset(RowNum).Value = Date – smushi Sep 24 '13 at 01:47
Error is "Method 'Range' of object '_Global' failed. – smushi Sep 24 '13 at 01:56
You need to add the Worksheet names to the beginning of all your range calls, especially now that I removed the Activate methods. – user2140261 Sep 24 '13 at 02:03
Something like sheets("SheetName").range("Processing_Date").offset(RowNum).value=Date – Sathish Kothandam Sep 24 '13 at 02:44
@SathishKothandam No A is the column that has the data in it, use the column values instead of the named ranges. – user2140261 Sep 24 '13 at 13:28

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

1 Answers1