Macro not working.

  • Thread starter Thread starter S Himmelrich
  • Start date Start date
S

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
 
I just tried your code, as posted, and (using XL2003) it works fine for me.

Rick
 
Works perfectly for me, do you have the code in the sheet you are running it
against or in thisworkbook or module?
 
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
 
Works perfectly for me, do you have the code in the sheet you are running it
against or in thisworkbook or module?
Click to expand...
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?
 
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?
 
Try

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

Or if your first row start in 1

Firstrow = 2
 
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
 
Read the Tips below the macro

For example

Select Case .Value
Case Is = "861126", "861444": .EntireRow.Delete
End Select
 
I think I need to ask this question differently......

In looking at the example that you provided to me and the following,
which is in your notes below the code:

Select Case .Value
Case Is = "jelle", "ron", "dave": .EntireRow.Delete
End Select
'Use Select Case if you want to check more values in the cell

I'm lost . . . .

Here is what works with one column:
'Finds all rows in Column "M" that don't have 'NCG' and deletes them.
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

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

'We loop from Lastrow to Firstrow (bottom to top)
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

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

I need to now add a routine to also evaluate another column for two
values....
 
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
 
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
 
See the tips section below the macro named
"Check a whole row or more columns"

There is a example there to check more columns
 
thank you for the update....I've swapped out the code and it works
great...thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run macro on all files in a specific folder 3
Delete Rows Based on Words 2
Delete Row If Cell Does Not Contain "text" 6
Need macro to delete rows not containing certain text 0
Windows XP Getting a Macro to run in multiple sheets 0
delete rows on value 3
Macro to delete some rows meeting criteria 5
Deleting Duplicate Row Macro Question 1

Back
Top