?
Solved

VBA Excel - Selection.ClearContents not resetting last row

Posted on 2011-03-01
1
Medium Priority
?
798 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 30

Accepted Solution

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

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

Sid
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

762 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