A lot of Excel functionalities are also available to be used in VBA – and the Autofilter method is one such functionality. If you have a dataset and you want to filter it using a criterion, you can easily do it using the Filter option in the Data ribbon. And if you want a more advanced version of Read More
How to Connect Power BI to a File/Folder in OneDrive
Here is a recent video that shows how to get data from a OneDrive file using the SharePoint Folder connector. Just build the new query, and then copy the steps of the new query and replace the Source (and maybe a few more steps) of your existing query. You may have to update your step Read More
How to Get Current Working Directory Using VBA
Use Application.ActiveWorkbook.Path for just the path itself (without the workbook name) or Application.ActiveWorkbook.FullName for the path with the workbook name. CurDir() is also very useful in some circumstands For example, When you open an Excel document D:\db\tmp\test1.xlsm: CurDir() returns C:\Users\[username]\Documents ActiveWorkbook.Path returns D:\db\tmp So CurDir() has a system default and can be changed. ActiveWorkbook.Path does not change for the same saved Workbook.
How to Check if a Worksheet Exists in Excel VBA
There is a couple of ways to do this. Solutions 1, use a for – next loop to check worksheet one by one. Function WorksheetExists(ByVal WorksheetName As String) As BooleanDim Sht As WorksheetFor Each Sht In ThisWorkbook.WorksheetsIf Application.Proper(Sht.Name) = Application.Proper(WorksheetName) ThenWorksheetExists = TrueExit FunctionEnd IfNext ShtWorksheetExists = FalseEnd Function Solution 2, Setting the non-existent sheet Read More
Excel VBA Auto-filter Based on Values of Another Cells
I and was trying to filter the data based a couple of cell values. Finally, I figured it out. Below, please see my sample. Filler Sheet “Trans” based on the values of C3, C5 and C6 from Sheet ” Reports”.
Make a Specific Column or Range to Absolute Values (Excel VBA)
See below for a sample to change all value in Column I to M to their absolute Values