Error handling

hemant86

New Member
Joined
Feb 10, 2015
Messages
29
Hi Folks,

I'm trying to use the below code:
Rich (BB code):
sub main()
.....
.....
Windows(e).Activate
    Columns("A:A").Select
    Selection.ClearContents

    Windows(n).Activate
    lRow = Range("AD" & Rows.Count).End(xlUp).Row
    Range("AD2:AD" & lRow).Copy
    Windows(e).Activate
    Range("A1").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
On Error GoTo NextStep3
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
    
   
NextStep3:
    iRow = 0
    While Sheets(1).Cells(iRow + 1, 1) <> ""
        Criteria_Val(iRow) = Sheets(1).Cells(iRow + 1, 1)
        iRow = iRow + 1
    Wend
    
    Workbooks("Reference Data").Sheets("OSPD CC List").Range("A1:X1").AutoFilter Field:=3, Criteria1:=Criteria_Val, Operator:=xlFilterValues
    Windows(k).Activate
    Sheets("OSPD CC List").Activate
    Lrow1 = Range("C" & Rows.Count).End(xlUp).Row
    
    Set qRng = Range("Q1:Q" & Lrow1)
    Set sRng = Range("S1:S" & Lrow1)
    
    
    Set uRng = Union(qRng, sRng)
    uRng.Copy
    
    Windows(n).Activate
    
    Range("BQ1").Select
    ActiveSheet.Paste
    
    Windows(k).Activate
    Selection.AutoFilter
End Sub
I'm getting runtime error in "Selection.SpecialCells(xlCellTypeBlanks).Select" step which is normal but I have also used Error handler to skip this step.
For some reason error handler is not behaving the same way it should.Its not going to the NextStep3: . But here is the catch...i have used error handler twice more in the same module
before this code and it's working fine. And when I'm trying to run the above code in a different module also it works fine.

Any assistance would be appriciated.

Thanks
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,102,856
Messages
5,489,302
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top