delete data rows in excel by using a macro?

[Marcia]

I need a macro to delete rows in a 1,000+ page excel report that are
identified by data in a specific column.

[Tom Ogilvy]

Sub DeleteRows()
Dim rng as Range
With columns(3)
Do
set rng = .Find(What:="ABC", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.EntireRow.Delete
else
exit do
End if
Loop
end Sub

--
Regards,
Tom Ogilvy

"Marcia" wrote:

> I need a macro to delete rows in a 1,000+ page excel report that are
> identified by data in a specific column.

[Marcia]

I have never created macros. Can you give me the step by step. Not sure
where to start

"Tom Ogilvy" wrote:

> Sub DeleteRows()
> Dim rng as Range
> With columns(3)
> Do
> set rng = .Find(What:="ABC", _
> After:=ActiveCell, _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> if not rng is nothing then
> rng.EntireRow.Delete
> else
> exit do
> End if
> Loop
> end Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Marcia" wrote:
>
> > I need a macro to delete rows in a 1,000+ page excel report that are
> > identified by data in a specific column.

[Tom Ogilvy]

It might be easier for you to select your data, then do
Data=>filter=>Autofilter

Select the column that you want to use to determine which rows to delete and
in that dropdown, select the value that specifies a row for deletion. Now,
only the header row and the rows to be deleted are visible. Select all the
data but the header row and do Edit=>Delete and if necessary, select
entirerow.

Now do Data=>filter=>Autofilter to turn off the autofilter and only the data
you wanted to retain should be left.

Practice this on a copy of your data.

If you want to learn how to use macros, check some of these out:


http://msdn.microsoft.com/office/und.../odc_super.asp

http://msdn.microsoft.com/office/und...d/default.aspx

http://www.mvps.org/dmcritchie/excel/excel.htm

http://www.mvps.org/dmcritchie/excel/getstarted.htm


-------------
Tutorial - the links to the VBA tutorials follow the Excel tutorial links.
http://www.mvps.org/dmcritchie/excel....htm#tutorials

--
Regards,
Tom Ogilvy


"Marcia" wrote:

> I have never created macros. Can you give me the step by step. Not sure
> where to start
>
> "Tom Ogilvy" wrote:
>
> > Sub DeleteRows()
> > Dim rng as Range
> > With columns(3)
> > Do
> > set rng = .Find(What:="ABC", _
> > After:=ActiveCell, _
> > LookIn:=xlValues, _
> > LookAt:=xlWhole, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False)
> > if not rng is nothing then
> > rng.EntireRow.Delete
> > else
> > exit do
> > End if
> > Loop
> > end Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Marcia" wrote:
> >
> > > I need a macro to delete rows in a 1,000+ page excel report that are
> > > identified by data in a specific column.