Home » VBA

Macro Mondays – How To Add A Break Point To Your Excel VBA Code

by —Leave a Comment

Hello, Excellers. Welcome to another #MacroMonday blog post in my Excel series. Today let’s look at breaking up our VBA code by adding a breakpoint to your Excel VBA code. If you’re an Excel user, you know that VBA code can be incredibly useful for automating tasks and customizing your spreadsheets. But what happens when your code isn’t working the way you want it to? One of the best ways to troubleshoot and fix your code is to add breakpoints so that you can see where in the code execution things are going wrong. In this blog post, we’ll show you how to add a pause or breakpoint to your Excel VBA code. Stay tuned!

First Things First – What Is A Breakpoint Or Pause In VBA?.

A breakpoint is a selected line of code that once it has been reached, your macro will be suspended. At this point you use the debugging environment to step through each line of code, halt the codes execution or view the status of your program. It is a great way to step though and debug your code without having to restart your code from the very beginning each time you want to debug.

There is no limit to how many breakpoints you have in your code. You cannot however add breakpoints to the following lines of VBA

  • nothing in them (blank lines).
  • Only code comments, usually wrtitten in green and begin with an apostrophe.
  • Dimensioning statements. (lines starting with DIM).
Excel Macro

 

How To Add Break Points To Your VBA Code.

Let’s get down to adding pauses or breakpoints into our VBA code. First, open the VBA environment by either hitting the Excel keyboard shortcut ALT+F11 or hit the Developer Tab and then select Visual Basic.

To set a breakpoint in your VBA Code, find the line you want to add to break to. Then, follow the instructions below.

  • Left-click in the grey bar to the left of the code.
  • A red dot should appear, subsequently the line of code will be highlighted in red.
MACRO MONDAYS ADDING pause or BREAKPOINTS TO YOUR VBA CODE
  • Your breakpoint will be inserted.

 

How To Remove Break Points From Your VBA Code.

Once you have finished debugging your VBA code it is really easy to remove the breakpoints you have inserted.

  • Left click on the breakpoint you want to remove
  • Your breakpoint will automatically be removed
MACRO MONDAYS CREATE AND REMOVE BREAKPOINTS IN EXCEL VBA CODE

 

How To Clear Multiple Break Points From Your Excel VBA Code.

If you have a lot of breakpoints in your VBA code as there is no restriction on how many you can use, then it maybe a challenge to individually go through your code and remove them one by one.  If you want to remove all of the breakpoints at once then you can use the debugging menu options.

MACRO MONDAYS ADDING BREAKPOINTS TO YOUR VBA CODE1
  • Select the Debug Menu
  • Select Clear All Breakpoints
  • Or use the keyboard shortcut CTRL+SHIFT+F9

So there you have it! How to add a break point or pause in your Excel VBA code. This should help you troubleshoot and fix any errors in your code. If you want more tips like this, sign up for my newsletter below and I’ll send them straight to your inbox. Thanks for reading!

If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not bookmark it?, Yes, it is updated EVERY Monday.

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel With Our Excel Online Courses

Copy A File From One Folder To Another – Macro

by —Leave a Comment

Have you ever needed to copy a file from one folder to another? If so, you’re in luck. In this blog post, we’ll show you how to do it using a macro. Stay tuned for our step-by-step instructions! I have to copy files from one folder to another every week. I find this a simple and effective way to carry out this task. So, today I want to show you how to write a simple Excel VBA Macro to copy a file from one location to another, or from a source folder to a destination folder. I run this code from Excel it does the hard work for me.

Excel Macro

Right, let’s get started on writing the simple VBA code.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice,. Either create a module to store your code either in your Personal Macro Workbook or store the code in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. Seeing as I want to reuse this over and over again, and only want excel to be open to rin the macro (not the workbooks I have to copy), then it makes sense to stores the code in my Personal Macro Workbook.

If you want to read more about your PMW then feel free to read my read my other Excel articles below.

Creating and Updating Your Personal Macro Workbook

Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Step 1. Starting The Macro.

First, insert a new module to store the code. In the VBA Editor hit Insert Module in your PMW as in the screen shot below.

vba to copy files from one folder to another

Once the module is inserted, then it is time to name the macro. Type Sub the the name of the macro. In this example I name the macro Copy_My_File. Notice once you name the macro Excel inserts the End Sub line of code into the editor. All other lines of code are stores between these two lines.

Here is a screen grab of the the full VBA macro. you can copy the full set of code at the end of this article to use. Always back up your work before running any code.

Step 2. Declare Variables.

The first piece of code is a DIM statement. Two variables are declared. The declaration of variables simply creates a memory container for these values in Excel. I am declaring the Source folder and the Destination folder as below.

Dim SoureFile As String
Dim DestFile As String

Step 3. Set The Source File.

The next step in the macro is to set the source file. This is the full file path and extension to file you want to copy. Update this line of code to reuse in your scenario with the full file path of the file you want to copy from one folder to another.

SourceFile = “C:\Users\EXCEL\Documents\Source\MyFile.xlsx

Step 4. Set The Destination File.

Next, set the destination for the copied file. This again is the full file path of the folder you want to copy the file to. Also update this line of code for your particular scenario.

DestFile = “C:\Users\EXCEL\Documents\Destination\MyFile.xlsx

Step 5. VBA Copy Files From One Folder To Another.

Step 5 is the line of code that copies the file from one folder to another. The variables of the source and destination file are supplied already. The FileCopy VBA statement has two arguments. The Source and Destination. This is the statement that name copies the file from one location to another location.

Note If you try to use the FileCopy statement on a file that is currently open, an error occurs. This is a really useful statement as I do not need to open the source or destination file open to carry out this process.

vba copy file from one folder to another.

Step 6. Ending The Macro.

The final step in the code is tp end the macro with the End Sub line of code once the file has been copied. The final line of code is already in the editor. Now, it is time to test the macro. My favourite part. Let’s go!.

So, this articles covered how to use vba copy files from one folder to another. I do hope you found this useful. The code is below to copy and use for your own Excel projects. Simply replace the Source and Desintation variables withyour own filepaths. Always backup your work before running any VBA code.

‘Macro by how to excel at excel.com
Sub Copy_My_File
Dim SoureFile As String
Dim DestFile As String
SourceFile = “C:\Users\EXCEL\Documents\Source\MyFile.xlsx
DestFile = “C:\Users\EXCEL\Documents\Destination\MyFile.xlsx
FileCopy SourceFile, DestFile
End Sub

copy file from one folder to another

Macro Mondays – Automatically Protect A Worksheet When You Close Your Excel Workbook

by

Hello Excellers, it is time for some more cool code in the #macromondays series. It can be a pain to remember to protect your worksheets every time you close your Excel workbook. But with a few simple steps, you can automatically protect them for added security. In this post, we’ll show you how to protect a workbook in Excel automatically. the process requires a simple but effective Excel VBA macro. So read on and learn how to make your data extra safe!

So do you like qtching Excel tips and working along with a solution. If the answer is yes, then I invite you to subscibe to my YouTube channel it is free with Excel tips every week that a quick and simple to follow. Quick tips for busy people. Click the link below to take you direct to my channel. So, looking forward to seeing you over there!.

So, back to #macromonday and today let’s look at automatically triggering a worksheet to be password protected as you close the workbook. Yes, this article is useful if you are updating and distributing an Excel workbook, but find your self un-protecting, updating and the protecting an worksheets over and over again. (I know I do. Every week and every month). With this macro, you can automate this process as you close the workbook, the specified password is applied. How convenient!.

What Does The Macro Do?

This Macro automatically password protects a worksheet as the Excel workbook closes. (i know it so cool).

How Does It Work?

The code is triggered by the BeforeClose event, which occurs before the workbook closes. If the workbook has been changed, then this event occurs before the user is asked to save changes by Excel. So, when decide to close the Excel workbook the VBA code is activated. Excel automatically protects the specified worksheet with the password you supply in the VBA Code. Finally, Excel saves the workbook.

Let’s go right ahead and write the macro to proect your Excel workbook automatically.

password protect when closing workbook

Step 1. Activate The Visual Basic Editor.

Activate the Visual Basic Editor by pressing ALT+F11, in the Project Window find your project or workbook name and click the plus sign next to it to see all of the worksheets in it, then select ThisWorkbook.

Step 2. Using The BeforeClose Event.

This is the core of the VBA code. Go ahead and select the BeforeClose event in the drop down list. It is by doing this we specifically tell Excel which sheet we want to protect ( in this example it is “Sheet1”). To make the code more applicable to your Excel project then this is where you change the

Step 3. Supply The Password.

The next step is to supply the password to use to protect the worksheet. (If you do not specify a password you can trigger the protection of the sheet. Consequenty users can easily just select the un-protect option to gain access to edit the worksheet).

Step 4. Close The Workbook And End The Macro.

The final step in this process is to save the Excel workbook and close it . This is where the macro ends. Al that is left is to test your new macro. Why not copy the code below and give this one a go.

Copy The Code To Protect A Workbook In Excel Automatically.

Feel free to use the cde and alter it for your own use in your own Excel workbook solution. Note- always back up your excel files before running any code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(Sheet1).Protect
Password = “mypassword”
ActiveWorkbook.Close

End Sub

Now that you know how to write a Excel vba Macro to protect a workbook in Excel automatically, be sure to sign up for my newsletter below so you can receive more tips like this one straight to your inbox. In the meantime, if you have any questions or feedback about this tutorial, please don’t hesitate to leave me a comment below and I will get back to you as soon as possible. Thanks for reading!

If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel With Our Excel Online Courses

Macro Mondays – Delete All Rows That Contain A Specific Text String

by

Hello Excellers, time for some Excel Macro fun again.  Today’s Excel Macro tip is one I have been looking forward to sharing with you for a while. Are you looking for a quick way to delete all the rows with specific text in your Excel spreadsheet?. If so, you’ll be glad to know that there is an easy way to do this. In this blog post, we will show you how to delete all rows that contain a specific text string. We will also provide some tips on how to use this technique. So if you’re ready, let’s get started!

This is a question I get asked quite regularly, and it is a really useful piece of code to know.  The most common way to tackle this type of problem is is to loop through the entire column in your Excel worksheet. If a cell in the column contains the specific text then instruct Excel to delete the whole row.  Seeing as Excel will shift rows upward by default on deletion, then the most logical way to do this is start at the bottom and work upwards.

This looping is quite a slow process which works great for smaller data sets as it is time consuming and slow to execute. It fails somewhat with larger data sets. Even optimising the code by turning off screen updates with the (Application Objects ScreenUpdating set to false) and Calculation properties set to Manual for the following reasons it is still slow. The mains reasons for this speed issue are below.

  1. The Delete Row command triggers Excel to recalculate, which if there are a lot of formulas in your worksheet is time consuming.
  2. Looping through all of the cells in a column or even the used cells in a column is also time consuming.

Use Auto Filter

So let’s assume we have the larger data set, and want to carry out this procedure in a more efficient way.  Well, I choose to use the Autofilter Method. This method is faster, but the data is required to be set up in a specific way. The data must be set up in a table format, with the first row containing field headers or column names.  (Which mine is!).

What Does The Macro Do?

This Macro will identify and filter all of the rows that contain the text string your specify, then delete them.

Step 1. Open Visual Basic Editor Insert A New Module.

So, the first step in this macro is to open Visual Basic. There are two ways to do this. Hit ALT +F11 or Developer Tab | Visual Basic | Insert Menu | Module. The module can be inserted into the current Excel workbook (this is typically used when the macro is specific to that workbook), or in your Personal Macro Workbook (PMW). The PMW is useful if you want to be able to run the macro when any Excel workbook is open. So, in this case, the macro COULD be used on any Excel workbook, so makes sense to save it in my PMW.

So, as soon, as Excel starts any macro saved in this location become available to use. The final part of the step is to the Excel macro, this this example the macro has the name DeleteRowsMeetingCriteria. Notice Excel inserts the End Sub line of code automatically. Any other lines of code are written between these two.

Step 2. Turn Off Filters In Active Worksheet.

We specify we are using the Active Workskeet, to ensure no other worksheet is affected by the macro actions..The Auto Filer is turned off (in case it is on) by Excel.

Step 3. Apply The Filter.

The Filter is applied to our data range which is set to Column A, down to the last row with data. The word to filter is Total. the data is now are filtered on any rows that contain “Total”, in Column A, (or Column 1).

Step 4 . Delete Rows!

Any rows now that are visible in the filtered data are deleted, using the SpecialCells(12).EntireRow.Delete but we have specified that we are offsetting the deletion by 1 cell. This is to accommodate the field or column headers which we do not want to delete.

Step 5. Remove AutoFilter.

In this last step the autofiter is removed once again to return the data set back. The macro then ends with the End Sub line of code.

Finally, it is time to test the Excel macro to delete rows with specific text.

excel delete rows with specific text

Copy The Code.

Feel free to copy the code below to use in your own Excel workbooks. Please backup any Excel workbooks before you run any code.

Sub DeleteRowsMeetingCriteria()
With ActiveSheet
.AutoFilterMode = False
With Range(“A1”, Range(“A” & Rows.Count).End(xlUp))
.AutoFilter 1, “Total”
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

More Excel Tips?

Sign up for my Excel Newsletter for three free Excel tips every month. Join thousands of other Excel users in the community. Come Say hello!.

Excel tips

Macro Mondays – How To Write A Macro To Delete Rows If A Cell Is Blank

by

Hello Excellers.  If you’re an Excel user, you know that one of the most tedious tasks is deleting rows if a cell is blank. Well, worry no more! With a simple macro, you can delete any number of rows automatically, based on whether or not a specific cell is blank. In this post, we’ll show you how to write the macro and put it to use. So read on to learn how to make your Excel life a little bit easier!

I use this Excel Macro on a regular basis. I receive a really large data dump every week, and I have to filter out blank records based on Column B. If the cell in column B is empty then the entire rows is deleted. So, a few lines of Excel VBA code has me sorted.

What Does The Macro Do?

The Macro deletes an entire row if the corresponding cell is Column B is blank. For example, if the cell B4 is blank then the whole row (or record is deleted).

delete rows in Excel

How Does It Work?

The Range.SpecialCells method is used for this macro. This method returns a range object that represents all cells that match the value and and specified.  In this case we are looking to return a range object which returns all blank cells in Column B.

Let’s get coding!. Delete Blank Rows In Excel.

MACRO MONDAYS DELETE ROW IF CELLS ARE BLANK

Step 1. Starting The Macro.

First, open Visual Basic, hit ALT +F11 or Developer Tab | Visual Basic | Insert Menu | Module.  There are now two options to save the Macro.

  • Store it in the current Excel workbook you are working on creating.
  • Save the Macro in your Personal Macro workbook.

In this instance I can re use the macro over and over again. So, by storing it in my Personal Macro Workbook it wl be available to use whenever the Excel application is opened. Insert the module and name the macro. In this example I have named the Macro DeleteMyEmptyRows.

Step 2. Turn Off Error Handling

The next step is to turn of error handling in Excel.  This enables the Macro to continue running even if it comes across an error in its execution.  In a more complex project, this would not be advised as we would want to know detail about errors,  but in such a small piece of code, it just allows it to run without error.

Step 3. Identify Blank Cells And Delete The Rows.

Next blank cells are identified in the Active Worksheet Column B. xlBlanks is used to identify the blank cells. The rows containg the blank cells are deleted entirely.

Step 4. Test The Macro!.

Finally it is time to test the Macro.. I told you it was a small piece of VBA code didn’t I?.

Want To Copy The Code To Delete Blank Rows In Excel?

So, do feel free to copy the entire code. Just remember to back up your work before you run any Macro.

Sub DeleteMyEmpyRows()
On Error Resume Next
ActiveSheet.Columns(“B”).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

That’s all there is to it! If you want to delete rows in Excel based on whether or not a cell is blank, just use the macro code we provided and customize it to your needs. Be sure to subscribe to our newsletter so you can stay up-to-date with all of the latest tips, tricks, and tutorials from our team here at Excel How To. Thanks for reading and happy deleting!

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share THREE Excel Tips every month. Receive my free Ebook, 50 Excel Tips on joining.

Excel Macro

How To Excel At Excel – Macro Mondays Blog Posts.

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel With Our Excel Online Courses

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar
Share
Additional Info