VBA Excel - Selection.ClearContents not resetting last row

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
victoriaharryAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Try this

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

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.