Solved: Last used cell

[satyen]

Hello. Im trying to write to file -but everytime I manually delete data from cells and file is empty(except header row) it doesn't start writing from A2, which is the next free row after the Header row. Instead it writes some wheredown column A. here is the code.

[VBA] Set IWorkbook = Workbooks.open(FolderName & "IFiles.xls"_ Format:=xlDelimited)

IWorkbook.Sheets(1).usedRange

If Not IWorkbook is Nothing then
IWorkbook.activate
With IWorkbook.Sheets(1)

rowsTmp = UsedRange.Rows.Count+1
.Cells(rowTmp, 1 ) =wName
.Cells(rowsTmp, 2) = Now
IWorkbook.close Save Changes:=true
End With

End if

[/VBA]

Thanks in advance

[Bob Phillips]

You use rowTmp at one stage, rowsTmp at another.

ALWAYS USE OPTION EXPLICIT!!!!!!!!!!!

[satyen]

Sorry this is a typo both should be rowsTmp. This is a snippett of code from a larger piece so Option Explicit is used at the begininning.

Any ideas?

[Bob Phillips]

Then it is because Excel still thinks it is the ld usedrange.

Try checking from the bottom up to the last used row.

[satyen]

. sorry this is still unresolved, I made a mistake. Im unsure how to do this xld.

[satyen]

I tried this, it still doesn't work: [VBA]rowsTmp = Range("A2" & Rows.Count).End(xlUp).Row[/VBA]

[Simon Lloyd]

. sorry this is still unresolved, I made a mistake. Im unsure how to do this xld.

Removed from the Solved list!

[Bob Phillips]

I tried this, it still doesn't work: [VBA]rowsTmp = Range("A2" & Rows.Count).End(xlUp).Row[/VBA]

That should be

[vba]

rowsTmp = Range("A" & Rows.Count).End(xlUp).Row
[/vba]

or

[vba]

rowsTmp = Cells(Rows.Count, "A").End(xlUp).Row
[/vba]

[satyen]

Everytime the macro is run it should add to the next row down. The header row should be kept. i tried this - but no joy [VBA]rowsTmp = Cells(Rows.Count + 1, "A:2").End(xlUp).Row[/VBA]

[Ago]

remove the ":2".
just copy what xld wrote. it should work

[satyen]

it doesn't seem to work

[Ago]

[VBA]
rowsTmp = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(rowsTmp, "A").Select

[/VBA]

[satyen]

works perfect , thank u very much!