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
Bob Phillips
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?
Bob Phillips
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!
Bob Phillips
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
remove the ":2".
just copy what xld wrote. it should work
satyen
05-29-2008, 10:48 PM
it doesn't seem to work :(
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.