Category: VBA macros

Macros in Excel for beginners, VBA excel examples, Excel VBA programming, VBA macros and tutorials

VBA macro to remove duplicates from each individual column [Excel]

vba-macro-remove-duplicates-each-column

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() ‘ […]

Delete entire row if cell is blank or contains [Excel 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

remove-white-spaces-VBA-macro

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:

VBA code to print a string in double quotes in excel

VBA-code-string-in-double-quotes

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 […]

VBA macro to remove special characters from a string in excel

excel-macro-output-remove-special-characters

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 […]

VBA code to read a character within a string

VBA_Macro_read_single_character_within_string

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 […]

VBA macro to replace string between two delimiters within a string

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, “]”) […]