VBA macros - Examples - Programming

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]

January 12, 2017

Posted by SRINI S Microsoft Office MS Excel VBA macros

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()
' Below VBA code removes the duplicates from each individual column in excel sheet
' Open the sheet in your workbook where you want to remove duplicates and run this macro
Columns.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Sub removeDuplicatesFromEachIndividualColumn()

' Below VBA code removes the duplicates from each individual column in excel sheet

' Open the sheet in your workbook where you want to remove duplicates and run this macro

Columns.RemoveDuplicates Columns:=1, Header:=xlNo

End Sub

If you want […]

Search for multiple strings within a cell [Excel VBA macro]

January 9, 2017

Posted by SRINI S Microsoft Office MS Excel VBA macros

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

Delete entire row if cell is blank or contains [Excel VBA]

August 28, 2016

Posted by SRINI S VBA macros

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

August 27, 2016

Posted by SRINI S VBA macros

VBA code to remove white spaces from a string in excel. Simple method is, use Replace() function.

stringToPrint = Replace(stringToPrint, " ", "")

1	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

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

August 27, 2016

Posted by SRINI S VBA macros

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)

1	Chr(34) & stringToPrint & Chr(34)

Example: Below is the VBA macro to print a string in double quotes (quotation marks (” “))

Sub printStringInDoubleQuotes()
Dim strinToPrint
stringToPrint = "string in double quotes"
MsgBox (Chr(34) & stringToPrint & Chr(34)) ' Printing the string in double quotes
End Sub

Sub printStringInDoubleQuotes()

Dim strinToPrint

stringToPrint = "string in double quotes"

MsgBox (Chr(34) & stringToPrint & Chr(34)) ' Printing the string in double quotes

End Sub

Output:

VBA macro to remove special characters from a string in excel

August 27, 2016

Posted by SRINI S MS Excel VBA macros

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 Macro for “selecting all shapes” in excel sheet

April 8, 2016

Posted by SRINI S MS Excel VBA macros

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

VBA macro to save each sheet in a workbook to seperate CSV files

June 11, 2015

Posted by SRINI S MS Excel VBA macros

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

May 2, 2015

Posted by SRINI S VBA macros

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

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 the string is too long and […]

VBA macro to replace string between two delimiters within a string

April 12, 2015

Posted by SRINI S VBA macros

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, "]") ' position of end delimiter
stringBwDels = Mid(originalString, firstDelPos + 1, secondDelPos - firstDelPos - 1) 'extract the string between two delimiters
replacedString = Replace(originalString, stringBwDels, stringToReplace)
MsgBox ("replaced string is " + replacedString) ' message shows replaced string
Close #1 ' closing the file
End Sub

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, "]") ' position of end delimiter

stringBwDels = Mid(originalString, firstDelPos + 1, secondDelPos - firstDelPos - 1) 'extract the string between two delimiters

replacedString = Replace(originalString, stringBwDels, stringToReplace)

MsgBox ("replaced string is " + replacedString) ' message shows replaced string

Close #1 ' closing the file

End Sub

« Older posts

Newer posts »