I used your suggestion, but still have the same issue. I've included
all the code - perhaps you some suggestions ?
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim lRec As Long
Dim lRecRow As Long
Dim lLastRec As Long
Dim lastRow As Long
If Target.Address = Me.Range("CurrRec").Address Then
Application.EnableEvents = False
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")
With historyWks
lastRow = ActiveCell.SpecialCells(xlLastCell).Row
lLastRec = lastRow
End With
With inputWks
lRec = .Range("CurrRec").Value
If lRec > 0 And lRec < lLastRec Then
lRecRow = lRec + 1
.Range("D5").Value = historyWks.Cells(lRecRow, 3)
.Range("D7").Value = historyWks.Cells(lRecRow, 4)
.Range("D9").Value = historyWks.Cells(lRecRow, 5)
End If
End With
Application.EnableEvents = True
End If
End Sub
On Nov 2, 9:00 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> As I said, you can use the xlLastCell method.
> it considers cells that WERE used as "reserved".
> It's kind-f like if you were to hit "undo", the deleted rows would return,
> so it considers the deleted rows as "reserved" until "undo" is no longer
> possible, like when you exit and re-open the file.
>
> Another option would be to check multiple columns..
> but the same problem would exist. If the contents of THAT cell were
> removed....
>
> I suggest going with the:
> LastRow = ActiveCell.SpecialCells(xlLastCell).Row
>
> perhaps before saving you can sort the data so you can get rid of any
> empty rows that are created...
>
> Paul
>
>
>
> ----- Original Message ----
> > From: JMac <jsvi...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Tue, November 2, 2010 7:53:25 AM
> > Subject: Re: $$Excel-Macros$$ Paste to Next empty Row
>
> > I beleive the problem is that if the data in column A gets deleted by
> > accident, it sees that row as empty and overwrites the whole row. Is
> > there a way to work around this ?
>
> > Thank-you
>
> > On Nov 1, 11:21 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> > > Jeff,
>
> > > There are lots of ways of finding the "next" empty row.
> > > In your case, is it possible that column "A" does not always have a value?
> > > also... lLastRec = lastRow - 1 will give you the PREVIOUS row instead of
> > > the
> > > NEXT row...
> > > Perhaps that's what is causing the problem.
> > > Wouldn't you use:
>
> > > lLastRec - lastRow + 1
>
> > > ??
>
> > > What I have often used is:
>
> > > LastRow = ActiveCell.SpecialCells(xlLastCell).Row
>
> > > Now, if during the execution of your macro, you're removing rows,
> > > then this method won't always work accurately.
> > > Let's say you have 10 rows of data.
> > > the above line will return 10.
> > > Now delete 5 rows.
> > > It will STILL Return 10.
> > > until you exit the file and re-open it.
> > > THEN it will return 5...
>
> > > Still...
>
> > > hope this helps,
>
> > > Paul
>
> > > ----- Original Message ----
> > > > From: JMac <jsvi...@gmail.com>
> > > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > > Sent: Mon, November 1, 2010 10:47:34 AM
> > > > Subject: $$Excel-Macros$$ Paste to Next empty Row
>
> > > > I've got a workbook in which a user inputs values into cells on sheet
> > > > 1, and then the info is copied to a log sheet. I'm using the following
> > > > code to find the next empty row in the log sheet to paste the info:
>
> > > > With historyWks
> > > > lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1,
> > > > 0).Row - 1
> > > > lLastRec = lastRow - 1
> > > > End With
>
> > > > From time to time, I'm told that an existing entry has been
> > > > overwritten with new data.
>
> > > > Should I be using something different to find the next empty row ?
>
> > > > Thanks
>
> > > > Jeff
>
> > > > --
>
> >>----------------------------------------------------------------------------------
>
> > > >-
> > > > Some important links for excel users:
> > > > 1. Follow us on TWITTER for tips tricks and links :
> > > >http://twitter.com/exceldailytip
> > > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > > <><><><><><><><><><><><><><><><><><><><><><>
> > > > Like our page on facebook , Just follow below link
> > > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
> >Hide quoted text -
>
> > > - Show quoted text -
>
> > --
> >----------------------------------------------------------------------------------
> >-
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below link
> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&...-
> >Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
