Solved

VBA Excel - Selection.ClearContents not resetting last row

Posted on 2011-03-01
1
783 Views
Last Modified: 2012-06-21
Hi,

I have an excel macro that clears the contents of the spreadsheet then imports some data from word tables into the spreadsheet. It works fine the first time but the next time it appears to have issues with the way I'm finding the last popualted row. It seems to remember the previous last row and starts popualting the rows were the last run ended. Any ideas on how to reset it would be appreciated
 Some code snippets are below

'Clear cells
Cells.select
Selection.ClearContents

Find = 0
lastRow = 6

For i = 1 To TableCount
        'Check that first column in the document has header Req Name
        If Left$(WordApp.Activedocument.Tables(i).cell(1, 1).Range.Text, 8) = "Req Name" Then
            WordApp.Activedocument.Tables(i).Range.Copy
            Sheet1.Range("A" & lastRow).PasteSpecial xlPasteValues
            Find = 1
            lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        End If
    Next
0
Comment
Question by:victoriaharry
1 Comment
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35014317
Try this

lastRow = ActiveSheet.Range("A" & rows.count).end(xlup).row+1

Sid
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now