Posts in category: vba

If you need to reference other files or folders within an Excel sheet, trying to predict what the path will be can be challenging. The easier option is to just have the user select the file or folder from their computer, and then have the path get pasted into a cell. To do this, you will need to use visual basic (VBA) for Excel to be able to get this value for you. In this post, I’ll show you how you can do that.

Creating the VBA code to select a file or folder

There are a couple of variables that need to be setup for this code. One is for the file or folder selection, and the other for the actual path. In the below example, I’m going to use the folder picker option. I’ll also disallow multiple selections to ensure the path value can easily be pasted into Excel:

Dim folder As FileDialog

Dim path As String


Set folder = Application.FileDialog(msoFileDialogFolderPicker)

folder.AllowMultiSelect = False


If folder.Show = -1 Then

path = folder.SelectedItems(1)

End If


If path = "" Then Exit Sub


If Right(path, 1) <> "\" Then path = path & "\"

Range("mypath") = path

The folder.show = -1 line is simply to make sure that the user has clicked the button. And if they do, the macro will just pull in the path of the selected item (since multiple selections are disallowed). Towards, the end of the code, there is also a backslash that is added in case it isn’t included within the path. If you were dealing with a file selection, this wouldn’t be necessary.

The last line of code assigns the path to a named range in your spreadsheet called ‘mypath’. You will need to create this to ensure the path goes to the right cell.

The above code will work just as well if you need to select a single file. The only difference is rather than referencing the msoFileDialogFolderPicker, you would want to access the msoFileDialogFilePicker. You can also remove the line that adds the backslash. And you would probably want to change the name of the variable from folder to file.

Create a button to run the macro

One thing you’ll want to do when setting up this macro is to add a button. This way, the user can just click the location next to where the path will be go. To create a button in Excel, go to the Insert tab. Then, select the drop-down menu for Shapes, and then select a square or rectangle to create a button. Once you have created it, right-click and select Edit Text. Here, you can type in a description for the button, such as Select Folder. Next, you can right-click the button and select Assign Macro. Then you should see the following dialog box, where you can select the macro you just created:

Once you’ve assigned the macro, you will be able to just click it so that it runs.

Selecting multiple file and folder paths

If you want to have the user select multiple paths, the easiest solution is just to have multiple places where you can select files or folders. The good news is you don’t need to create multiple macros for this. Instead, you just need to modify the code so that it looks at which row the button you click on is in. This requires using the application caller. Instead of using a named range, I’ll refer to a set column and the row that the button is on. To adjust the macro, I need to add a variable for the selectedRow and assign its value as follows:

Dim selectedRow as integer

selectedRow = Activesheet.Shapes(Application.Caller).TopLeftCell.Row

Assuming that I want to put the path in column A, this will now be my last line of code:

Range("A" & selectedRow) = path

Here is the full code:

Sub selectfolder()

Dim folder As FileDialog
Dim path As String
Dim selectedRow As Integer

Set folder = Application.FileDialog(msoFileDialogFolderPicker)
folder.AllowMultiSelect = False

selectedRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row


If folder.Show = -1 Then
path = folder.SelectedItems(1)
End If

If path = "" Then Exit Sub

If Right(path, 1) <> "\" Then path = path & "\"

Range("A" & selectedRow) = path


End Sub

Since it looks at the selected row, you can just copy the button to multiple rows. You don’t need to create a different macro. However, since it looks at the top left cell, it’s important to keep the button within just a row. Don’t let it bleed into another row. Otherwise, you may populate the path into the wrong cell. Here’s how the buttons look in my example:

It doesn’t matter if the buttons span multiple columns. Using the code above, they simply shouldn’t be extending into another row. In the above example, I have just copied the button multiple times. Now, when a user click on them and makes a selection, it will paste the selected path into the corresponding cell in column A.

If you liked this post on How to Use VBA Code So a User Can Select a File or Folder Path, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

You can add lots of automation to your spreadsheet by utilizing visual basic (VBA) and running macros. One of the more common macros you might run include loops. In particular, using for loops in VBA can allow you to cycle through a range of data, check for criteria, and then execute commands. Or, you can also use it to do calculations and to compute totals. There’s a lot of potential with running loops in VBA. In this post, I’ll show you how you can get started with them.

Looping through data in Excel to highlight blank or incomplete data

In the following data set, I have some missing values where there is an (*) in place of a value in column D:

I’m going to loop through the column that shows the change value and I will look for the (*) values.

To get started, launch VBA from the developer tab or hit ALT+F11. Then, insert a new module. I’m going to call this subprocedure ‘cleanup’. I’ll start with declaring variables for the worksheet, individual cell I’m looping through, an integer to track how far along I am in the range, and an integer for the last cell within the range.

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row




End Sub

In the above code, I’ve assigned the worksheet variable to the sheet that I’m on. And I set the lastcell variable to the last row in the sheet. This is the same as if you were press F5->Special->Last Cell. This makes it easy to determine where the data set ends, and gives me an endpoint for my loop. There are other types of loops you can use where you don’t need to specify an end. But this reduces the risk of you getting into a never-ending loop.

As for the loop itself, it will look like this:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Interior.Color = vbRed
            
        
        End If
        

Next i

How it works is it uses the i variable to start from 1 and go all the way until the lastcell variable. In this example, that relates to 92 (the last row in my data set).

Then, it assigns the cl variable to each cell in column D as I go through that range. In the first instance, the cl variable is D1, then D2, and so on, until you reach the end (D92).

Next, it evaluates if the value of that cell is (*). If it is, then it highlights the entire row in the color red. If I run this macro, this is what my sheet looks like afterwards:

It did the job well, as you’ll notice everything that had a value of (*) in column D, the entire row ended up getting highlighted in red. There are other colors you can use and other things you could have done. Next, I’ll show you how you can just delete these rows entirely.

Deleting rows while looping through data in Excel

Removing rows seems simple enough, but it is a little tricky here. For instance, to remove the row rather than to just highlight it, that involves a simple line of code (in my example I’m referring to my cl variable):

cl.EntireRow.Delete

If I run the macro with that code, this is what happens to my worksheet:

I’ve left the red highlighting in place to show you that there are still many rows that should have been deleted and that weren’t. So did the macro simply not work?

The problem lies with how it was set up. In the current macro, I’m moving down from one row to the next. If I’m at row 10 and delete it, then the next row I move on to is row 11. However, the issue is that once I delete row 10, everything moves up a spot. So what was previously row 11 now becomes row 10, causing me to effectively skip over that row and miss it. Now my macro no longer evaluates it.

There are a couple of ways to fix this. One can be that if I delete the row, I can adjust my i variable so that it deducts one so as not to skip over the next row. Just by adding a line of code, you can adjust for that issue:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            i = i - 1
            
        
        End If
        

Next i

The i = i -1 line of code will reset the i variable back down a spot when a row is deleted. This will now prevent the macro from jumping over a row.

However, there’s another option you can use, and that’s looping through the data in the opposite direction.

Looping through the data backwards and utilizing the step keyword

In the first example, I looped through the data from row 1 to the last row. Here’s my full code for that:

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            
        
        End If
        

Next i



End Sub

I’m going to adjust this and go in reverse order. However, it’s not as simple as specifying start from the bottom number and go to row 1. You need to give VBA a bit more information. This is where you can use the Step keyword. By using that, you can specify in which direction you want the loop to go, and whether it should go 1 row at a time or jump multiple rows.

Here’s how the loop looks like if I want VBA to jump backwards one row at a time from the bottom:

For i = lastcell To 1 Step -1


    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            

        End If
        

Next i

If you want to jump by 5 rows, you would use Step -5. However, because I want to evaluate each row, -1 is what I’ll use in this example. By running this loop, now the highlighted rows are all deleted:

Since the loop is starting from the bottom and working its way up, it doesn’t matter that I’m deleting rows; it doesn’t impact the rows above and so no adjustment to the i variable is necessary.

If you like this post on How to Create For Loops in VBA, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.