How to delete multiple rows based off a value on another tab?

**mitchinphx** · 10-28-2010, 07:59 PM

I have attached a sample to make life easier and thank you in advance. I did look through the help and found lots of ways to delete rows but none referencing a value on another tab that changes. Most were based on static values, but i digress..

I have a list of properties on a tab called "PropList" that have a unique number value in column A. I want to create a macro to delete all of the rows with values in column A that don't match the value that the user enters on the "Info" tab in cell "A4". But I don't want to ever delete the first 4 rows of data as it contains other information. The real report has thousands of rows and tens of columns of data and this will help prune and save file size.

Thank you!!

**rylo** · 10-28-2010, 08:26 PM

Hi

Here goes.

Sub aaa()
  Sheets("PropList").Activate
  For i = Cells(Rows.Count, 1).End(xlUp).Row To 5 Step -1
    If Cells(i, 1).Value <> Sheets("Info").Range("A4").Value Then Cells(i, 1).EntireRow.Delete shift:=xlUp
  Next i
End Sub

rylo

**Leith Ross** · 10-28-2010, 10:32 PM

Hello mitchinphx,

With that much data to check, you may want a faster macro. This macro first sorts the data using the values in column "A" and filters the column using the value from "A4" on the "Info" sheet. The filtered rows, if there are any, will then be deleted.

Sub DeleteRows()

  Dim Data As Variant
  Dim InfoWks As Worksheet
  Dim PropWks As Worksheet
  Dim Rng As Range
  Dim RngEnd As Range
  
    Set InfoWks = Worksheets("Info")
    Set PropWks = Worksheets("PropList")
    
      Data = InfoWks.Range("A4").Value
      
      PropWks.AutoFilterMode = False
      
      Set Rng = PropWks.Range("A4")
      Set RngEnd = PropWks.Cells(Rows.Count, Rng.Column).End(xlUp)
      If RngEnd.Row < Rng.Row Then Exit Sub
      
      Set Rng = PropWks.Range(Rng, RngEnd)
      
        Rng.Sort _
          Key1:=Rng.Cells(1, 1), Order1:=xlAscending, Header:=xlYes, _
          MatchCase:=False, Orientation:=xlTopToBottom
        
        Data = Format(Data, "00000")
        Rng.AutoFilter Field:=1, Criteria1:=Data, VisibleDropDown:=True
        
      On Error Resume Next
      Set Rng = Rng.SpecialCells(xlCellTypeVisible)
      If Err = 0 Then Rng.EntireRow.Delete

      PropWks.AutoFilterMode = False
        
End Sub

**mitchinphx** · 10-29-2010, 02:01 AM

Thank you! But with that working I discovered a problem. The number on the info sheet displays as 03002 because of formatting, but it is actually read as 3002. So when the filter reads 3002 and doesn't find it in the prop list because there it is 03002 it deletes everything. Can a ltrim function where a 0 is added to the left if it is only 4 numbers be added somehow? There are property numbers that are 10000, 10001, etc. so i don't always need to add a 0.

Thanks again,
mitchinphx

**Leith Ross** · 10-29-2010, 04:23 AM

Hello mitchinphx,

I have another way of doing this without using a filter that is still very fast. I am heading off to bed, but will pick this up in the morning.