• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 802
  • Last Modified:

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
0
victoriaharry
Asked:
victoriaharry
1 Solution
 
SiddharthRoutCommented:
Try this

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

Sid
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now