VBA macro to remove duplicates from each individual column in excel sheet. This simple macro, just single line syntax, treats each column as a separate entity and removes all duplicates from each column and keeps only unique values. Also see VBA macro to delete empty columns VBA macro code to remove duplicates from each column Sub removeDuplicatesFromEachIndividualColumn() ‘ […]
Category: VBA macros
Macros in Excel for beginners, VBA excel examples, Excel VBA programming, VBA macros and tutorials
Below is the macro if you want to search for multiple strings within an excel cell check if a cell contains multiple text strings check if a cell contains one or more sub-strings I used four strings to search for in below macro, you can increase or decrease the count based on your requirement. VBA […]
Excel VBA code to delete entire row if the cell is blank or empty or cell is ‘xx’ or cell contains ‘xx’. Case 1: Delete entire row, if cell is empty The macro checks the cell value, if it is empty or blank, then it deletes the entire row, else the cursor moves to the cell […]
VBA code to remove white spaces from a string in excel. Simple method is, use Replace() function. stringToPrint = Replace(stringToPrint, ” “, “”) Example: Sub removeWhiteSpaces() Dim stringToPrint stringToPrint = “Please Remove White Spaces” stringToPrint = Replace(stringToPrint, ” “, “”) MsgBox (stringToPrint) ‘ Printing the string after removing white spaces End Sub Output:
Below is the VBA code to put double quotes (quotation marks) for a string in excel. We can use CHR() function to print special characters like double quotes in excel VBA. Chr(34) & stringToPrint & Chr(34) Example: Below is the VBA macro to print a string in double quotes (quotation marks (” “)) Sub printStringInDoubleQuotes() Dim […]
Simple VBA macro code to remove special characters from a string in excel. You can remove either all special characters or choose which special characters to remove from the string. This macro retrieves each character from the string and checks whether it belongs to A-Z,a-z,0-9 or a space.If it belongs then it stores them into another […]
Use below VBA macro to “select all shapes” within a current excel sheet. Sub selectAllShapesInExcel() ActiveSheet.Shapes.SelectAll End Sub Advantages Saves your time of selecting the shapes manually
Excel VBA macro to save each sheet in a workbook to seperate CSV files.The macro creates seperate CSV file (with sheet name) for each sheet of workbook. Note: Before you run the below macro, change “C:\test\” in 12th line to your folder path where you want to save the created CSV files for each worksheet. […]
VBA code to read a character within a string. Case 1: Suppose the string is “ Excel VBA macro”, and you want to read a character before BA (i.e. V) within that string. VBA code: Sub readCharWithinString() Dim inputStr Dim charac inputStr = “Excel VBA macro” charac = Mid(inputStr, 7, 1) MsgBox (“The character is ” & charac) End Sub When you run above macro, below message box is displayed. Case 2: Suppose […]
Excel VBA macro to replace string between two delimiters within a string or text line. This article also explains the usage of Replace function in excel VBA macro. VBA code: Sub replaceStringBetweenTwoDelimeters() ‘ Macro to replace string “gethow” with “GetHowStuff” in string “welcome to [gethow]” Dim stringBwDels As String, originalString As String, firstDelPos As Integer, secondDelPos As Integer, stringToReplace As String, replacedString As String originalString = “welcome to [gethow]” stringToReplace = “GetHowStuff” firstDelPos = InStr(originalString, “[“) ‘ position of start delimiter secondDelPos = InStr(originalString, “]”) […]