+ Reply to Thread
Results 1 to 2 of 2

Excel VBA - Filter/Unfilter After Deleting Data with Last Row and Last Column

  1. #1
    Registered User
    Join Date
    12-22-2014
    Location
    Seattle
    MS-Off Ver
    Office 2010
    Posts
    2

    Excel VBA - Filter/Unfilter After Deleting Data with Last Row and Last Column

    I have to generate a report from time to time and write code to filter for blanks in column I and delete all the entries containing blanks, then unfilter. I wrote code to find the last row and last column and then do the filtering/un-filtering. It all works until the un-filtering part (the last line). How do I unfilter my table, which now has new dimensions, aka new values for finalRow and finalColumn (and thus rng as well)?

    Sub Macro4()
    Dim finalRow As Long
    finalRow = Range("A" & Rows.Count).End(xlUp).Row
    Dim finalColumn As Integer
    finalColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim rng As Range
    Set rng = Range(Cells(finalRow, 1), Cells(finalRow, finalColumn))


    rng.AutoFilter Field:=9, Criteria1:="="
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Select

    rng.AutoFilter Field:=9


    End Sub

  2. #2
    Registered User
    Join Date
    09-07-2013
    Location
    Brussels
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Excel VBA - Filter/Unfilter After Deleting Data with Last Row and Last Column

    Why don't you try a simple code like this:

    Range("yourRange").Select
    Selection.AutoFilter 'this action sets the filter
    ............. run the rest of your code here .....
    Range("yourRange").Select
    Selection.AutoFilter 'this action cancels the filter
    Range("A1").Select

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Filter Table as i type in textbox1 but when erasing the text filter should be unfilter
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-17-2014, 09:08 AM
  2. [SOLVED] Need: filter, paste info to new sheet, rename sheet, unfilter, hide column, filter next
    By esmithqg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2012, 04:49 PM
  3. Replies: 2
    Last Post: 04-08-2012, 09:43 PM
  4. Replies: 3
    Last Post: 03-15-2012, 10:01 PM
  5. VBA to actvate Unfilter after Auto Filter
    By Zimbo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2008, 02:10 AM

Tags for this Thread

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