Macro not working.

[S Himmelrich]

this macro doesn't work...it should delete any how that doesn't have
"NCG" in column 13, but doesn't work

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If UCase(Cells(r, 13).Value) <> "NCG" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[Rick Rothstein \(MVP - VB\)]

I just tried your code, as posted, and (using XL2003) it works fine for me.

Rick

 

[John Bundy]

Works perfectly for me, do you have the code in the sheet you are running it
against or in thisworkbook or module?

 

[Don Guillett]

Trimming perhaps.
Is NCG or ncg the only thing in the cell or part of a string?
Put a msgbox lastrow to test for the desired last row

 

[S Himmelrich]

Works perfectly for me, do you have the code in the sheet you are running it
against or in thisworkbook or module?

Objective was to put this in a macro...hence that is where it is and
not working. When all of you run this code, where do you put it?
Basically this is one of many, many steps I want to perform in a
single macro. What is the best way to proceed?

 

[S Himmelrich]

when trying this as a macro I get an error on Next r

 

[Ron de Bruin]

hi

See this page a for a lot of examples
http://www.rondebruin.nl/delete.htm

 

[Don Guillett]

Is that an answer to my questions

 

[S Himmelrich]

Your examples worked great, thanks.

hi

See this page a for a lot of exampleshttp://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm






- Show quoted text -

 

[S Himmelrich]

In your example for deleting rows you use the following code:

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

My first row is a header and I don't want it to be evaluated or
disrupted.....how do I change the code to not eval Row 1. I did what
I thought the right answer was and it didn't work so any thoughts?

 

[Ron de Bruin]

Try

Firstrow = .UsedRange.Cells(1).Row +1

Or if your first row start in 1

Firstrow = 2

 

[S Himmelrich]

this did the trick...thank you.

Firstrow = .UsedRange.Cells(1).Row +1

 

[S Himmelrich]

I've come accross another question for you. In regards to the
evaluating a row in a column and then identifying if it should be
deleted I'm now looking to evaluate column I and remove any row that
reflects "861126" or "861444" as well....can this be done within the
same loop? I tried copying the same code and changing values, but I
get compile errors as the varibles are already used / defined.....and
so forth...

For Lrow = Lastrow To Firstrow Step -1

'We check the values in the M column in this example
With .Cells(Lrow, "M")

If Not IsError(.Value) Then

If .Value <> "NCG" Then .EntireRow.Delete
'This will delete each row that doesn't have a
Value "NCG"
'in Column M, case sensitive.

End If

End With

Next Lrow

 

[Ron de Bruin]

I need to now add a routine to also evaluate another column

I see you only keep the NCG rows now
If .Value <> "NCG" Then .EntireRow.Delete

Do you want to check for more values in the same column or in other columns ?
Which columns and which values ?

Give more information please

 

[S Himmelrich]

The column evaluating "NCG" is good. I have another column that I
need to remove all rows with either "861126", "861444".

Thank you for your support.

Scott

 

[Ron de Bruin]

See the tips section below the macro named
"Check a whole row or more columns"

There is a example there to check more columns

 

[S Himmelrich]

thank you for the update....I've swapped out the code and it works
great...thank you.