Simple Question...

[tess457]

I need to search and filter an entire column in a database whose rang
is continually changing by the day. What is the best way of searchin
each cell in an entire column, and if the contents of the cell are no
what I am searching for, I want to delete the entire row? I am not sur
how to find the last row of data in the database, except by using th
"cntrl + *" function, and this selects the entire data range, so i
doesnt quite work. This is what I am curently using, but it doesnt see
to filter through the complete file..i have to run the macro a fe
times, and even then it deletes what it shouldnt.

Sub Filter()
Dim cel As Range, rng As Range
Set rng = Range("D2", Range("D50").End(xlUp))
For Each cel In rng
If cel.Value <> " " Then
cel.EntireRow.Delete
End If
Next cel

I would really appreciate anyones help or suggestions!! Thanks a ton..

Dusti

 

[Jamie]

One answer may be autofilter.
Click on any cell in your data list then click Data, Filter, Autofilter.
This will add a dropdown arrow next to each of the field headings. The drop
down list then gives you each entry in that feild and allows you to filter
out everything but that data. You can then delete that row.
Ctrl + END will move to the last cell in your spreadsheet.

 

[Gord Dibben]

Dustin

Option Compare Text
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
mything = InputBox("Enter search string")
LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "D").Value = mything Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

Gord Dibben Excel MVP



Wed, 1 Sep 2004 13:45:39 -0500, tess457

 

[tess457]

hey...right on..thanks alot guys...that was exactly what i was looki
for!