Delete rows without a specific value

**chriss** · 06-02-2008, 06:14 AM

Hi guys,

I have another problem that I hope you can help me with!

I have 2 spreadsheets of delivery information. Sheet 1 contains data of all stores in the UK with addresses. Sheet 2 containts contacts of all stores that promotional matrial needs to be sent to.

The only common cell in a column of 4 digit store numbers.

I need to delete all rows from sheet 1 that do not contain a value in the store number column that is displayed on sheet 2.

Hope that makes sense.

ps

for forum moderators or regular posters, where does your knowledge of Excel come from? Is it experience, or are there books / courses you can reccommend?

Many thanks,

Chris

**Portuga** · 06-02-2008, 12:18 PM

Hi,

One way to do this is to have an extra column in Sheet1 and use a vlookup on store numbers in sheet2.

If it find it, you will get the store number, if not, you will get the #N/A error.
If you apply an autofilter, you can filter by that column, on values that = #N/A and delete those rows.

**Leith Ross** · 06-02-2008, 12:22 PM

Hello Chris,

Change the store column and start row in the macro to match what you need.

Sub DeleteRows()

  Dim DelRows As Range
  Dim LastRow As Long
  Dim Rng As Range
  Dim StartRow As Long
  Dim StoreCol As Variant
  
    StoreCol = "A"
    StartRow = 2
    LastRow = Cells(Rows.Count, StoreCol).End(xlUp).Row
    
      LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
      Set Rng = Range(Cells(StartRow, StoreCol), Cells(LastRow, StoreCol))
      
        On Error Resume Next
        Set DelRows = Rng.SpecialCells(xlCellTypeBlanks)
        If Err = 0 Then DelRows.EntireRow.Delete Shift:=xlShiftUp
      
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross