Delete rows from sheet

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

I have up to about 5000 rows of data on a sheet, it is made up of original
and updated information about customers.

Column A is an ID number. What I want to do, by code, is to delete the
single entry customers (those who have no updated information).

I have sorted by column A to bring both original and updated together but am
now stuck.

I have tried inserting a column (A) and putting the following in to identify
the ones I want to keep but cannot figure out how to delete the remainder:

=IF(COUNTIF($B:$B,B2)>1,2,1)

Thanks in advance.

Gareth
 
click in B1
Data=>filter=>Autofilter

this will put a dropdown in all the columns of your data. Select the
dropdown in the column that has your formula

Select 1
now only rows with a 1 will be visible.

Select all these rows by using the left row labels. Select everything but
row 1, which I assume is the header row.

Do Edit=>Delete

this will delete only the visible data.

Do Data=>filter=>Autofilter to remove the filter

You should now have only the rows that had multiple records for the ID.
 
Tom
I can manage this 'manually' as you describe, but as I said I want to do it
using code.
Gareth
 
Gareth,

Do it once the manual way. Record a macro while you are doing it.

Massage the finished code and you have it always.

steve
 
My problem is that there are not always the same number of records so I
don't know what range to delete.

Any suggestions?
 
Doesn't have to be quite that complex.

Assume IDs are in Column A and formulas in Column B

Sub Tester10()
Range("A1").AutoFilter Field:=2, Criteria1:=1
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).EntireRow.Delete
Range("A1").AutoFilter
End Sub
 
Back
Top