Easy Way to Select and Delete Rows 1

[DanAuber]

Hi there - I'm looking for a fast way to code the following in VB
Find All rows with the text "OK" in column A (rows are sorted by column A so they are together)
Delete those Rows

It sounds simple but I need to allow for the fact that
A) There may not be ANY with OK in column A
b) There may only be one with OK in column A
c) There may be several with OK in column A

each of these scenarios seems to need slighly different code and that is making it a nightmare.

Has anyone got an easy way of doing this ?

Thanks for any help

 

[SkipVought]

Check out AutoFilter.

Select the ROWS containg data and DELETE.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[DanAuber]

Hi Skip - I can do the code to filter - what would the code be to "select the rows that have data" ?

Thanks

 

[BaldyMcFatFat]

I thin he is saying you don't need code but can do it manually just as quick. Unless you have several hundred lines you would need to delete all at once, I believe manually is just as efficient.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[AnotherHiggins]

something like

[A2].AutoFilter Field:=1, Criteria1:="=*ok*"
Range([A2], [A2].SpecialCells(xlLastCell)).EntireRow.Delete
Selection.AutoFilter

[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.

 

[SkipVought]

The CurrentRegion property defines ALL contiguous cell. Use the second row in the CurrentRegion thru the last row, using the Rows.Count property.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

You'll also waht to use SpecialCells(xlcelltypevisible).entirerow.delete

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

Skip - I've used the above (or equivalent) for a long time now on large recordsets and never had any trouble.

No need to specify visible - it only deletes visible anyway.

[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.

 

[SkipVought]

John,

That comment was not aimed at you. I was getting ready to leave the office, mulling over my last response and decided to add that suggestion. So I did not even see yours. Good to know, though. I hardly ever use the lastcell feature and I need to get familiar with more specialcells features.

Thanx!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Roonaldez]

Hi,

I am trying to do something very similar and am little stuck, I have tried to replicate the above code but it doesn't seem to delete the required data...see code below:

Range("O7").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-3]"
Range("O6").Select
ActiveCell.FormulaR1C1 = "REF 1"
a = Range("b7").End(xlDown).Row
Range("o77").AutoFill Destination:=Range("o7" & a), Type:=xlFillDefault
[o7].AutoFilter Field:=1, Criteria1:="=0"
Range([o7], [o7].SpecialCells(xlLastCell)).EntireRow.Delete
Selection.AutoFilter

I am trying to add a sum, and then based on that sum, delete all rows where the sum total is zero...the sum is in column o and is for a variable range, i only want rows ledft behind whereby there is a positive value.

Any help appreciated.

 

[AnotherHiggins]

Roonaldez: Please create a new thread for this question.

In that thread, please note if you have data in column N or if that column is empty.

[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.