Delete rows from sheet
 

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

Delete rows from sheet
 

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.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
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

Delete rows from sheet
 

Tom
I can manage this 'manually' as you describe, but as I said I want to do it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
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

Delete rows from sheet
 

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

"Gareth" wrote in message
...
Tom
I can manage this 'manually' as you describe, but as I said I want to do
it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
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

Delete rows from sheet
 

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

Any suggestions?

"steve" wrote in message
...
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

"Gareth" wrote in message
...
Tom
I can manage this 'manually' as you describe, but as I said I want to do
it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
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

Delete rows from sheet
 

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

--
Regards,
Tom Ogilvy


steve wrote in message
...
Gareth,

This code will figure out the number of rows; delete all the rows with a
"1", than turn the filter off. (watch for line wrap after Special Cells)

steve

===============================================
Sub DeleteMyRows()

Dim lrow As Long

lrow = Cells(Rows.Count, "A").End(xlUp).Row
' finds the last used row in column A

Range(Cells(1, 1), Cells(lrow, 1)).AutoFilter Field:=1, Criteria1:="1"
Range(Cells(2, 1), Cells(lrow,
1)).SpecialCells(xlCellTypeVisible).EntireRow.Dele te
Range(Cells(1, 1), Cells(lrow, 1)).AutoFilter
End Sub

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

Any suggestions?

"steve" wrote in message
...
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

"Gareth" wrote in message
...
Tom
I can manage this 'manually' as you describe, but as I said I want
to
do
it
using code.
Gareth

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
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