optimize the macro

[luvbite38]

Hi guys,

Can some kindly please adjust the following macro to work faster.
It is currently taking 10 to 15 seconds.

I am wondering if someone can help me make it faster...something like click and get....

any sort of help with me much appreciated.

Sub ThirdRanked()
'Call FindAllSkills
    
    
   Dim rFind As Range
Dim lRow As Long
Application.ScreenUpdating = False
If Sheets("ThirdRankFinder").Range("C3") = "" Then
MsgBox ("Select the Skill Category")
Exit Sub
Else
Sheets("AllSkills").Select
Rows("1:2").Select
    Selection.Delete Shift:=xlUp
 
Sheets("DataSheet").Range("GenInfo").Copy
Sheets("AllSkillS").Select
Range("A1").PasteSpecial xlPasteAll, _
    xlPasteSpecialOperationNone, False, True
    Application.CutCopyMode = False
     Columns("J:Xfd").Select
    Selection.ClearContents

With Sheets("DataSheet")
    Set rFind = .Range("A1:A" & Rows.Count).Find(What:=Sheets("ThirdRankFinder").Range("C5"), _
    LookIn:=xlValues, Lookat:=xlWhole)
       
       
       If rFind Is Nothing Then MsgBox "Selected Entry Not Found!": Exit Sub
    lRow = rFind.Row
    .Range(.Cells(lRow, 2), .Cells(lRow, Columns.Count)).Copy
       
       
       
       Sheets("AllSkills").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteAll, _
    xlPasteSpecialOperationNone, False, True
    Application.CutCopyMode = False
End With
Call FormatSkills1
    End If
    
    
    
    
' applying the filter for rank three
Sheets("AllSkills").Select
    Range("A2:J2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$J$8").AutoFilter Field:=10, Criteria1:="3"
    Columns("A:J").Select
    Range("A2").Activate
    Selection.Copy
    Sheets("ThirdRank").Select
    Columns("A:A").Select
    Range("A2").Activate
    ActiveSheet.Paste
    Range("A1:J1").Select
    Sheets("AllSkills").Select
    Range("A2:J2").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Sheets("ThirdRank").Select
    Range("A2").Activate

Application.ScreenUpdating = True
End Sub

 

[someone21]

Remove the .select and replace "" with vbnullstring

Eg

<code style="white-space: nowrap;"><code>
</code></code>

with Sheets("AllSkillS")
.Range("A1").PasteSpecial xlPasteAll, _
    xlPasteSpecialOperationNone, False, True
    Application.CutCopyMode = False
     .Columns("J:Xfd").ClearContents

End with

 

[luvbite38]

Thanks buddy,

Really appreciate it