Results 1 to 3 of 3

VBA code to read last filled row of data column, works, but doesnt work

Threaded View

  1. #1
    Registered User
    Join Date
    10-31-2013
    Location
    DC
    MS-Off Ver
    Excel 2010
    Posts
    36

    VBA code to read last filled row of data column, works, but doesnt work

    I'm trying to figure out the issue with my code. I have a spreadsheet where I'd like to run a macro. The number of rows changes constantly as every workbook I'll be working with has a different number of rows.
    I'm basically trying to get my VBA code to read the last row of a column that has been filtered out. When the filter is applied it will fill column B with a value of N. My current code works, however, at the end of the table, I'm getting 4 additional "N" values outside the table range or rows. I debug each step and I believe the code
    ActiveSheet.Range("$A$1:$R$" & lr).AutoFilter
    is causing the issue.
    I currently have this code:

    I was able to get it to read the number of rows existing in the workbook:

     
        lr = Cells(Rows.Count, 18).End(xlUp).Row
    ActiveSheet.Range("$A$1:$R$" & lr).AutoFilter Field:=13, Criteria1:=Array( _
            "Attachment does not exist in document place holder", _
            "Document place holder does not exist", _
            "More than one current version files exist in the document"), Operator:= _
            xlFilterValues 
         Range("B2:B" & ActiveSheet.UsedRange.Rows.Count + 1).SpecialCells(xlCellTypeVisible).Value = "N"
    However, at the end of the table, there are additional N values outside the table itself. I did a debug and I believe its the part highlighted in red that's giving me problems, especially the "1r". I attached a copy of a screenshot to show you what I'm talking about.

    How can I fix this? You'll see that there are 4 "N"s on the left side below the table.
    Attached Images Attached Images
    Last edited by mDevel; 11-15-2013 at 01:26 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro for copying & inserting a variable number of rows into a separate workbook
    By Gunther Maplethorpe in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-01-2013, 06:26 PM
  2. Replies: 2
    Last Post: 07-15-2012, 04:05 PM
  3. Macro to filldown rows
    By divkar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-25-2010, 11:13 AM
  4. Excel Filldown problem
    By Phil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2005, 09:05 AM
  5. Filldown Problem
    By Juan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-24-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1