Thursday, March 31, 2016

Fast Solution to Problem: Advanced Filter - deleting after the extraction?

Advanced Filter - deleting after the extraction?

I run an advanced filter where the Data Range is 3 columns of data and the number of rows changes.  The criteria is based on one piece of data that can be found in the third column. 
Problem: Once those records are identified and copied to a different location I would then like to have the information that was copied to be deleted from the Data Range.
Is there a way to do this easily?
I thought to set up a macro that I would run after the Advanced Filter runs, but the number of rows in the Data Range changes and there is a total cell at the bottom of Column B so I do not know how to have a macro adjust for different number of rows in
the data.  The only macros I have created have been on spreadsheets where the number of rows remains the same.
Thank you - Gayle

Keys to the Problem Advanced Filter - deleting after the extraction?

Download Error Fixer for Free Now

It looks like you are using Advanced Filter to do the copying and then AutoFilter with the same criteria to do the deletion?  And instead of deleting the moved drows you are just clearing them?
I think I would just use the one filtering method.
And I would generalise it by finding the row number of the last used row in column A.

Sub Rosedale()
' Rosedale Macro
  Dim rFilter As Range
  Set rFilter = Range("A3:C" & Cells(Rows.Count, "A").End(xlUp).Row)
  rFilter.AutoFilter Field:=3, Criteria1:="4"
  ' prepare the desitination area
  Range("D4:F" & LastRow+2).ClearContents
  ' copy the filtered results
  Range("D4").PasteSpecial xlValues
  ' clear the filtered results (if there were any)
  If rFilter.Columns(1).SpecialCells(xlVisible).Count>1 Then
  End If
End Sub

If you would prefer to delete the copied rows, replace the last 4 lines before the End Sub by
  Dim rToDelete As Range
  If rFilter.Columns(1).SpecialCells(xlVisible).Count>1 Then
    Set rToDelete = rFilter.Offset(1).Resize(rFilter.Rows.Count-1).SpecialCells(xlVisible)
    rFilter.AutoFilter  ' clear the filter
    rToDelete.Delete shift:=xlUp
  End If

Open Action Center by clicking the Start button Picture of the Start button, clicking Control Panel, and then, under System and Security, clicking Review your computer's status.

Make sure your computer has at least the minimum amount of random access memory (RAM) required to run the program that is displaying the error. Look for the program requirements online or in the packaging information.

Another Safe way to Fix the Problem: Advanced Filter - deleting after the extraction?:

How to Fix Advanced Filter - deleting after the extraction? with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your computer.  Open it, and it will perform a scan for your system. The errors will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in this software. Like dll downloading, junk file cleaning and print spooler error repair.

Related: How to Fix - 64g ssd with a 500g regular drive?,Allow Unhide Rows in Protected Workbook [Solved],[Solved] Get in Excel 2007 data from Access 2007 out of self-built Queries,[Solution] How can I temporarily disable 'service manager' to install Adobe flashplayer?,[Anwsered] When I try to watch a flash video, I am told occasionally that I don't have Adobe Flash.,Solution to Error: Black screen during boot sequence,[Solved] Can't restore Windows 7 64-bit from external hard drive,How to Fix - IE 11 Enhance Protect Mode reset issue with add-on's?,Solution to Error: Internet Explorer 9 update/install error - Error Code 80092004,Upgrading to IE 8 causes cookies to get deleted when starting IE [Anwsered],Solution to Problem: All programs try to start from windows component
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,How to Fix Error - Getting an error "not connected to the internet" while trying to install Samsung Kies?
,How to Fix - Internet Explorer shuts down and reopens tab when attaching to email or uploading files.?
,Fast Solution to Problem: Sending Error Message
,[Anwsered] Thinkpad 8611 Boot,How to Resolve - Svchost Helper?,Fast Solution to Problem: L30 101 Driver Windows 7,Troubleshooter of Error: Io Device,How to Fix Error - Dell Laptop Code 39?
Read More: Troubleshooter of Error: address bar drop down,Solution to Error: Adobe Reader X doesn't seem to be compatible,Adobe Setup Error 1402 \"Could not open key\" when trying to install CS4 [Solved],Tech Support: Advanced Tab is Blank,How to Fix Error - ADOBE AND ACROBAT?,a file called mDNSResponse.exe. is causing bonjour not to operate properly,what should I do?,A QUESTION USING THE "IF'S" Formula.,A continuos flashing window with which title is C:Windows\System32\cmd.exe, and has the following message: The syntax of the command is incorrect.,Acrobat compatibility issue and you tube problems____,ActiveX on IE 9 not loaded

No comments:

Post a Comment