Error Handling - Find Method

How to handle an error when the value which you are searching is not available in Excel sheet.

Sub Err_Hnd()

Dim M_Rng as Range
Sheets("My Sheet").Select
On Error Resume Next
Set M_Rng = Sheets("My Sheet").Columns(1).Find(What:=Test_PID, after:=Cells(1, 1), LookIn:=xlValues,
                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                    searchdirection:=xlNext, MatchCase:=False)
On Error GoTo 0

If M_Rng Is Nothing Then GoTo Err_H:
'''' if M_Rng has returned any value then your code execution starts from here
Err_H:
           '''' based on the error what has to be done
           ''' exit sub
End Sub

Below are the Error Handler Options in Excel VBA-

• On Error GoTo
  Enables the error-handling routine that starts at , which is any line label or line number. The specified line must be in the same procedure as the On Error statement.
• On Error Resume Next
  Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred. In other words, execution continues.
• On Error GoTo 0
  Disables any enabled error handler in the current procedure.

In the above example what we are doing is allowing the execution of the macro to proceed if an error occurs (i.e, using the On Error Resume Next )
Subsequently we are disabled the error handler using On Error GoTo  0
Now we are checking the Range variable if it contains Nothing, if yes then we take the code execution to the error handler label, otherwise the execution proceeds as usual.

Using Find Method in Code

Sometimes in Excel Macro coding, when you are required to find any specific cell value and execute the list of codes. It becomes extremely time taking when you loop through all cells in the excel column.The simple solution is to use the Find method in Excel Code.

Set rFound_Cell=Columns(2).Find(What:="$$", after:=Cells(1,1), LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False)

SRow=rFound_Cell.Row

What if there are multiple matching cells and you got to loop through each time the match occurs -
Use the Countif Function to establish the number of occurrence of string or value in the sheet and then loop it.

Dim rFound_Cell As Range
Set rFound_Cell = Range("B1")
Sheets("Sheet1").Select
For i = 1 To WorksheetFunction.CountIf(Columns(2), "($$$)")

      Set rFound_Cell = Columns(2).Find(What:="($$$)", after:=rFound_Cell, LookIn:=xlValues, _
                                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                                    searchdirection:=xlNext, MatchCase:=False)
      SRow = rFound_Cell.Row

Next i

How to Speed-up the macro execution

Sub Main_Prg()
Speedup_Macro True
'''' Your Code
'''
'''end of code
Speedup_Macro False
End Sub

Function Speedup_Macro(Chk As Boolean)
If Chk = True Then
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Macro is working....pls wait "
Else
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.StatusBar = ""
End If
End Function

Description -
Application.DisplayAlerts

The default value is True. Set this property to False if you don't want to be disturbed by prompts and alert messages while a macro is running; any time a message requires a response, Microsoft Excel chooses the default response.
When using the SaveAs method for workbooks to overwrite an existing file, the 'Overwrite' alert has a default of 'No', while the 'Yes' response is selected by Excel when the DisplayAlerts property is set equal to False.

Application.ScreenUpdating
Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.
Remember to set the ScreenUpdating property back to True when your macro ends.

Procedure to reduce the size of Excel files

Just wondering how to reduce the size of Excel files, frustrated with ever increasing excel file size without any idea, tried creating new copies still of no use. Well even I was a victim of this ever increasing file size in excel. After a lot of trial and error method and some technical analysis, I tried to put the steps in order so that it would be helpful for everyone.

Follow the below steps to reduce the size of excel files -

1. Before starting the procedure take a backup of the file
2. Unhide all sheets
3. Select all unused / blank columns and go to Edit>Clear>All
4. Select all unused / blank rows and go to Edit>Clear>All
5. Repeat the steps 2 & 3 for all the sheets
6. Save the file and close it and now check the file size, it would have reduce considerably.

The above steps work, believe me I have tested the same and after following the steps the file size reduced from 90 MB straight down to 5 MB !!!

Also some excel sheets would have a very small scroll bar irrespective of the contents of the sheet

Steps to correct the scrollbar issue –

Find the last used row and then select all the cells below this till end of the column

Select Edit > Delete and select Entire Row

Now Save the excel file and then reopen it

Now the scrollbar would turn out to be of normal length

Hope this works and the file size would be reduced thereby saving time to open the file and also save the disk space.

Thanks for reading this article J

Excel Functions Library

Excel Functions Library

Good Website to understand basics of Excel Macro - 2

http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/

Autofilter error - Autofilter Method of Range class failed

How to handle error in AutoFilter Method while using Excel Macros -

Error - "autofilter method of range class failed"

Reasons -

because the rows might be blank

It's caused because your data is not laid out for AutoFilter. To use AutoFilter the data has to be in a table layout with a Header, with no empty rows or columns.

Solution-

select the complete range of values and then do selection.autofilter