Delete Rows that do not contain data

[cdulong]

I am working in excel and I am trying to create a macro that will delete all rows that do not contain a specific value.

The problem I am running into is that the text I am looking for is in the middle of the cell and not sure show to perform the lookup. The column that contains the data I am looking for is column "A". There is about 4000 rows I need to look throuh

Any help would be great.

Thanks,
cdulong

 

[AnotherHiggins]

Turn on your macro recorder.

Select all cells ([Ctrl]+[A] or click in the square to the left of "A" and above "1").

Turn on Auto-Filter.

Choose 'Does not Contain' and whatever you want to filter on.

Select all rows from 2 to the end of the sheet (I'm assuming you have a header row in Row 1).

Right click > Delete, answer Yes when it asks if you want to delete entire rows.

Post back with the code that was generated and we'll help you clean it up, if needed.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[cdulong]

Sub Macro1()
'
' Macro1 Macro
'

'
Cells.Select
Range("G3").Activate
Selection.AutoFilter
Windows("Book1").Activate
Windows("log.log").Activate
ActiveSheet.Range("$A$1:$A$3643").AutoFilter Field:=1, Criteria1:= _
"<>*/owa/ &prfltncy*", Operator:=xlAnd
Rows("2:3643").Select
Selection.Delete Shift:=xlUp
Range("B63").Select
ActiveSheet.Range("$A$1:$A$42").AutoFilter Field:=1, Criteria1:="<>**", _
Operator:=xlAnd
Selection.AutoFilter
Range("I8").Select
ActiveWindow.SmallScroll Down:=-42
End Sub

I noticed in the macro that it references the end row. some times this sheet can have a lot more or less. i would need to take this into account.

Thanks,
cdulong

 

[Gavona]

Is there data in every row in column A?

Record another macro:
select the first cell with data in column A (the one with the field heading)
Ctrl-Shft-Down will select all data in the column
Data Autofilter etc

OR if there isn't data in every row:
select the first cell with data in column A (the one with the field heading)
Ctrl-Shft-End will select the entire data range (and possibly a bit more but that is a different issue)
Data Autofilter etc

Just to see the effect: repeat but having started the macro recorder click the Relative Address button on the Stop Toolbar. The carry out the steps as above.

Hopefully this will help you to see how to select a variable number of cells, and how to use Offset to specify a relative address (you want to delete the row after the heading row, down to the last row and delete them).


Gavin

 

[Gavona]

I would expect you to get results a bit like this

Sub Macro1()
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="=*v*", Operator:=xlAnd
    Range("A7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    ActiveSheet.ShowAllData
End Sub
Sub Macro2()
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="=*d*", Operator:=xlAnd
    ActiveCell.Offset(2, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
End Sub

And I ended up with

Sub Macro1()
    Range(Selection, Selection.End(xlDown)).AutoFilter _
        Field:=1, Criteria1:="=*v*", Operator:=xlAnd
    Range(ActiveCell.Offset(1, 0), _
        ActiveCell.End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.ShowAllData
End Sub

Note that recorded code includes lots of unnecessary selections.
I added the type visible bit though it is not strictly necessary - just prefer to be explicit. Manually this is accessed from a toolbar button or Edit,Goto,special...

Gavin

 

[AnotherHiggins]

Sub Macro1()
    dblAvailRows = ActiveSheet.Rows.Count '1

    ActiveSheet.AutoFilterMode = False '2
    Cells.AutoFilter Field:=1, Criteria1:="<>*/owa/ &prfltncy*" '3
    Rows("2:" & dblAvailRows).Delete '4
    ActiveSheet.AutoFilterMode = False '5
End Sub

1) Calculate the total number or rows in Excel. I do this because 2007 have more rows than 2003 and I'm not sure what version you're on. Besides, this makes it more future-proof.

2) Ensure that Autofilter isn't already turned on

3) Apply the filter

4) Delete all rows that are left after the filter - You'll notice that I use the total number or rows in the workbook which sidesteps the problem of varying record-set lengths

5) Turn Auto Filter off again

If you don't want to see the screen flicker as it does the work, wrap the code in:
[tab]Application.ScreenUpdating = False
[tab]'Your code
[tab]Application.ScreenUpdating = True


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.