Macro Question

[Airbiskit]

I have a raw report that requires formatting to become the finished article. I can use recorded macros for most of it.

With the report I need to delete every row with 'No' in a certain column, the number of rows changes with every report so a simple recorded macro wouldn't work.

How can I can manipulate the recorded macro to include a command that deletes the rows with 'No' in a column?

My progamming is amateur at best so please be gentle.

Many Thanks
S

 

[xlbo]

record yourself adding an autofilter to your dataset and filtering for 'No' and then deleting all the records in 1 go, then resetting the autofilter

Post the code back and someone here will show you how to make it dynamic

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

 

[Airbiskit]

Thanks Geoff...Heres the code

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 11/04/2006 by vp
'

'
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="No"
ActiveWindow.SmallScroll Down:=-9
Rows("2:36").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=5
Range("I11").Select
End Sub

 

[xlbo]

ok - here ya go:

Sub Filter_Out_The_Nos()
Dim lRow As Long
[COLOR=green] 'find last row of dataset[/color]
 lRow = Cells(65536, 5).End(xlUp).Row
  Range("A1:H" & lRow).AutoFilter Field:=5, Criteria1:="No"
  Range("A2:H" & lRow).EntireRow.Delete
  ActiveSheet.AutoFilterMode = False
End Sub

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

 

[Airbiskit]

Thanks Geoff that works a treat.

I have been messing about trying to expand the code to include other criteria so instead of just "NO" it picks up #N/A too.

Now I have tried a few ways of doing it but all don't work. Please advice.

Thanks again

S

 

[xlbo]

try recording again and making that selection using the 'Custom' option on the autofilter - view how the code changes and apply to your code.

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