Slow Macro Modify

dear sir royUK.pls check my previous post.these macros not same..
http://www.excelforum.com/excel-prog...very-slow.html

Try this first

Sub Sticker()
    Dim LR As Long
    
    LR = Range("C" & Rows.Count).End(xlUp).Row
    
    Range("E1:H" & LR).AutoFilter Field:=1, Criteria1:="="
    Range("H2:H" & LR).ClearContents
    Range("E1:H" & LR).AutoFilter
    
    Range("C1:D" & LR).AutoFilter Field:=1, Criteria1:="AMBATTUR B"
    Range("D2:D" & LR).Replace What:="*-", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
    Range("C1:D" & LR).AutoFilter
    
    Call Lotremove

End Sub

If this does what you want, we can then address Lotremove.

P.S. This is very similar to the last stages of your previous post on the subject.

http://www.excelforum.com/excel-prog...very-slow.html

The main difference is the use of AutoFilter

Cheers

sir Marcol it's not working.if column "E" has any values then same row column "H" value should clear.but column "E" don't have any value then column "H" values no need to clear.this macro cleaning column "H" all values.

Try changing this line

Range("E1:H" & LR).AutoFilter Field:=1, Criteria1:="="

To

Range("E1:H" & LR).AutoFilter Field:=1, Criteria1:="<>"

Is that what you want?

thanks a lot sir Marcol now it's work very well very fast.this is last answer or u will do something to second macro also..

Re: Sub Lotremove()

1/. Are you just wanting to clear all formulae in Column "M" and replace with their values?

2/. What are you trying to do with Columns "E" & "H" ?
Seems to me that they were dealt with in Sub Sticker(), No?

Originally Posted by Marcol

Re: Sub Lotremove()

1/. Are you just wanting to clear all formulae in Column "M" and replace with their values?

2/. What are you trying to do with Columns "E" & "H" ?
Seems to me that they were dealt with in Sub Sticker(), No?

column "M" i want to keep only first 6 characters.
Columns "E" & "H" all empty cells should fill with " ' " apostrophe.

Just what this has to do with keeping the first 6 characters in a cell, I don't know?

For Each cell In Range("M3", Range("M" & Rows.Count).End(xlUp))
    cell.Value = Val(cell.Value)
    cell.Offset(0, -7).NumberFormat = "0"
Next cell

However try this

Sub LotRemove()
    Dim LastRow As Long
    
    LastRow = Range("M" & Rows.Count).End(xlUp).Row
    
    ' Temporary helper Column
    Range("IV2").Formula = "=Trim(Left(M2,6))"
    Range("IV2:IV" & LastRow).FillDown
    Range("IV2:IV" & LastRow).Copy
    ' Destination Column
    Range("M2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                             SkipBlanks:=False, Transpose:=False
    ' Clear Temporary helper Column
    Range("IV2:IV" & LastRow).Clear
    
    Range("M2:M" & LastRow).Offset(0, -7).NumberFormat = "0"
    
    LastRow = Range("L" & Rows.Count).End(xlUp).Row
    Range("E1:E" & LastRow).AutoFilter Field:=1, Criteria1:="="
    Range("E2:E" & LastRow).Replace What:="", Replacement:="'", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
    Range("E1:E" & LastRow).AutoFilter
    
    Range("H1:H" & LastRow).AutoFilter Field:=1, Criteria1:="="
    Range("H2:H" & LastRow).Replace What:="", Replacement:="'", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
    Range("H1:H" & LastRow).AutoFilter
    
End Sub

1/. What purpose does filling blank cells with an apostrophe serve? I'm curious.

2/. There is nothing in this code, as can clearly be seen, that could not been done without a macro,

3/. For one-offs you should consider using Excel as it comes straight out of the box.


Hope this helps.

thanks u very much sir Marcol.now this macro work very fast.thanks a lot.i have try to give REP+ but it's saying (You must spread some Reputation around before giving it to Marcol again).sorry for that.