How to delete based on criteria?

[egghi]

Hi,

I have 5 columns on the spreadsheet plus rows of header and footer for each page. I would like to delete the header and the footer and just keep the "meat" on the spreadsheet.

Here is a sample of the sheet:

Good Morning Distribution
Page 1
Date: 07/25/2006

BT SalesName Account# Client Reason
123 Nick HAN1234 Jim Bad products
123 Ben HAJ2345 Ellen Not enough varieties
131 Dennis HAM3123 Dean Poor service


Client Number: 3
Sales Code: Returns
Follow up: Luanne White

I was trying to delete any row that does not have the value "HA" in column C. However, my code did not seem to work:

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue <> "HA%"
' This will delete the rows without "HA" in the Range("C1:C1000")

With ActiveSheet
.Range("C1:C1000").AutoFilter Field:=1,
Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub


Thank you in advance!!

 

[xlbo]

I think you need to use a * as the wildcard

DeleteValue <> "HA*"


Use the macro recorder to check in any case...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[egghi]

Hi Rdgs,

I tried "*", but it did not work. I think it's the <> (not equal to) causing me problem. Any suggestion?

Thank you!

 

[PHV]

Just a guess:
DeleteValue = "<>HA*"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

yep - use the macro recorder as I have already suggested

record yourself manually filtering for Custom>"Starts With" "HA"

view the results
learn

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question