PDA

View Full Version : Solved: Last used cell



satyen
05-28-2008, 01:01 PM
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.

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



Thanks in advance

xld
05-28-2008, 01:32 PM
You use rowTmp at one stage, rowsTmp at another.

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

satyen
05-28-2008, 01:43 PM
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?

xld
05-28-2008, 02:29 PM
Then it is because Excel still thinks it is the ld usedrange.

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

satyen
05-28-2008, 02:32 PM
. sorry this is still unresolved, I made a mistake. Im unsure how to do this xld.

satyen
05-29-2008, 02:15 PM
I tried this, it still doesn't work: rowsTmp = Range("A2" & Rows.Count).End(xlUp).Row

Simon Lloyd
05-29-2008, 02:19 PM
. sorry this is still unresolved, I made a mistake. Im unsure how to do this xld.Removed from the Solved list!

xld
05-29-2008, 02:22 PM
I tried this, it still doesn't work: rowsTmp = Range("A2" & Rows.Count).End(xlUp).Row

That should be



rowsTmp = Range("A" & Rows.Count).End(xlUp).Row


or



rowsTmp = Cells(Rows.Count, "A").End(xlUp).Row

satyen
05-29-2008, 02:30 PM
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 rowsTmp = Cells(Rows.Count + 1, "A:2").End(xlUp).Row

Ago
05-29-2008, 03:38 PM
remove the ":2".
just copy what xld wrote. it should work

satyen
05-29-2008, 10:48 PM
it doesn't seem to work :(

Ago
05-29-2008, 11:54 PM
rowsTmp = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(rowsTmp, "A").Select

satyen
05-30-2008, 01:36 PM
works perfect , thank u very much!