How to guides- Gethowstuff

Combine multiple columns data to single column [Excel VBA]

May 8, 2017

If you want to combine multiple columns data to a single column using excel VBA, then use below code. Below macro combines data of 10 columns and displays it in first column. Change numOfColumns variable value as per your requirement.

Sub combineMultipleColumnsData()
' This macro combines data in multiple columns into a single column
' In this example, i took numOfColumns=10, you can change it as per your requirement
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer
Dim numOfColumns As Integer
Set rng = ActiveCell.CurrentRegion
iCol = 1
numOfColumns = 10
Range("A1").Activate
Cells.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
lastCell = rng.Columns(iCol).Rows.Count + 1
For iCol = 2 To numOfColumns
Application.Columns(iCol).SortSpecial SortMethod:=xlPinYin
    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 1)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub

Sub combineMultipleColumnsData()

' This macro combines data in multiple columns into a single column

' In this example, i took numOfColumns=10, you can change it as per your requirement

Dim rng As Range

Dim iCol As Integer

Dim lastCell As Integer

Dim numOfColumns As Integer

Set rng = ActiveCell.CurrentRegion

iCol = 1

numOfColumns = 10

Range("A1").Activate

Cells.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

lastCell = rng.Columns(iCol).Rows.Count + 1

For iCol = 2 To numOfColumns

Application.Columns(iCol).SortSpecial SortMethod:=xlPinYin

Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut

ActiveSheet.Paste Destination:=Cells(lastCell, 1)

lastCell = lastCell + rng.Columns(iCol).Rows.Count

Next iCol

End Sub

Remove blank rows inside cell [excel VBA]

May 6, 2017

We can create new line/row inside a cell by pressing ALT+ENTER in MS Excel. If you come across a situation where you want to remove blank rows/lines inside the cell, then you can’t remove them using trim method. Below code helps you to remove them automatically using VBA code.

Sub removeBlankRowsInsidecell()
Dim cellWithBlankRows, cellWithoutBlankRows
'Remove blank rows inside cell
cellWithBlankRows = ActiveCell.Value ' example
cellWithoutBlankRows = Replace(cellWithBlankRows, Chr(10), "") ' removing blank lines inside cell
End Sub

Sub removeBlankRowsInsidecell()

Dim cellWithBlankRows, cellWithoutBlankRows

'Remove blank rows inside cell

cellWithBlankRows = ActiveCell.Value ' example

cellWithoutBlankRows = Replace(cellWithBlankRows, Chr(10), "") ' removing blank lines inside cell

End Sub

In the above code Chr(10) […]

[Excel VBA code] Convert a string to Uppercase letters

May 6, 2017

Excel VBA code to convert a string to uppercase letters. It converts all lowercase letters to uppercase.

Sub convertStringToUpperCase()
'macro to convert a string to uppercase letters
exString = "helLo how Are yOU" ' Example string
stringAfterConversion = StrConv(exString, vbUpperCase) ' Converting the string letters to uppercase
MsgBox ("string after conversion to uppercase is - " + stringAfterConversion)
End Sub

Sub convertStringToUpperCase()

'macro to convert a string to uppercase letters

exString = "helLo how Are yOU" ' Example string

stringAfterConversion = StrConv(exString, vbUpperCase) ' Converting the string letters to uppercase

MsgBox ("string after conversion to uppercase is - " + stringAfterConversion)

End Sub

Output:

VBA code to capitalize each word of a string [Excel]

May 6, 2017

Below is the Excel VBA code to capitalize each word of a string.

Sub capitalizeEachWordOfString()
'macro to capitalize each word of a string
exString = "helLo how Are yOU" ' Example string
stringAfterCapitalized = StrConv(exString, vbProperCase) ' capitalizing each word of the string
MsgBox ("string after its each word is capitalized is - " + stringAfterCapitalized)
End Sub

Sub capitalizeEachWordOfString()

'macro to capitalize each word of a string

exString = "helLo how Are yOU" ' Example string

stringAfterCapitalized = StrConv(exString, vbProperCase) ' capitalizing each word of the string

MsgBox ("string after its each word is capitalized is - " + stringAfterCapitalized)

End Sub

Output:

Main domain .htaccess file affects addon domain [SOLVED]

April 19, 2017

If the rewrite rules in main domain’s .htaccess file are affecting the addon domain, then add below code (at end) in main domain’s .htaccess file. You can find the .htaccess file at public_html/ <IfModule mod_rewrite.c> RewriteEngine on RewriteCond %{HTTP_HOST} ^(www\.)?addondomain\.com Rewriterule .* – [L] </IfModule> Note: Replace addondomain in above code with your addon domain In […]

DOS command to list files in a directory into a text file

March 11, 2017

Looking for DOS command to create file listing of a folder and its sub-folders into a text file? Here are the simple steps. Before using the commands, do below steps Open Command Prompt Goto the folder path from where you want to list the files. Run below commands In below examples, C:\Main-Folder\Folder> is the folder path […]

Strip trailing whitespaces from string [Python]

February 21, 2017

If you want to remove extra whitespaces from start and end of a string, use strip() method in Python. strip() method removes all default whitespaces (spaces and tabs \t) and returns the copy of the string. Python code to strip trailing whitespaces from string

string = "  xyz is equal to xyz  " # string with whitespaces (spaces and tabs)
print "string with trailing whitespaces is \"" + string + "\"" # printing string with trailing whitespaces
string = string.strip() # strip trailing whitespaces
print "string after stripping trailing whitespaces is \"" + string + "\""  # printing the string after stripping trailing whitespaces

string = " xyz is equal to xyz " # string with whitespaces (spaces and tabs)

print "string with trailing whitespaces is \"" + string + "\"" # printing string with trailing whitespaces

string = string.strip() # strip trailing whitespaces

print "string after stripping trailing whitespaces is \"" + string + "\"" # printing the string after stripping trailing whitespaces

Note: Code works in both Python 2 and 3 Output […]

Python: Position of next occurrence of a sub-string

February 21, 2017

Python code to get the position of next or second occurrence of a sub-string within a string.

# Below code finds the position of second occurrence of "xyz"
string = "xyz is equal to xyz"
firstSubPos=string.find("xyz") # get the position of first substring "xyz"
secondSubPos=string.find("xyz", firstSubPos+1) # get the position of second occurence of substring "xyz"
print "position of second occurence of substring \"xyz\" is " + str(secondSubPos) # print the position

# Below code finds the position of second occurrence of "xyz"

string = "xyz is equal to xyz"

firstSubPos=string.find("xyz") # get the position of first substring "xyz"

secondSubPos=string.find("xyz", firstSubPos+1) # get the position of second occurence of substring "xyz"

print "position of second occurence of substring \"xyz\" is " + str(secondSubPos) # print the position

Note: Above code works in both Python 2 and Python 3 Output:

Python: Replace/remove string between two delimiters/characters

February 20, 2017

This Python code helps you to to replace a string between two delimiters or characters delete/remove string between two characters or delimiters Python code to replace string between two delimiters

# This macro replaces nivas in below string with silveri
string = "sri[nivas]"
firstDelPos=string.find("[") # get the position of [
secondDelPos=string.find("]") # get the position of ]
stringAfterReplace = string.replace(string[firstDelPos+1:secondDelPos], "silveri") # replace the string between two delimiters
print stringAfterReplace # print the string after sub string between dels is replaced

# This macro replaces nivas in below string with silveri

string = "sri[nivas]"

firstDelPos=string.find("[") # get the position of [

secondDelPos=string.find("]") # get the position of ]

stringAfterReplace = string.replace(string[firstDelPos+1:secondDelPos], "silveri") # replace the string between two delimiters

print stringAfterReplace # print the string after sub string between dels is replaced

Python code to delete/remove string between two characters/delimiters Replace 5th line of above code with below one

stringAfterReplace = string.replace(string[firstDelPos+1:secondDelPos], "") # remove the string between two delimiters

1	stringAfterReplace = string.replace(string[firstDelPos+1:secondDelPos], "") # remove the string between two delimiters

Note: This code works in both […]

Python script to extract string between two delimiters

February 19, 2017

Simple python script to extract sub string between two delimiters of a string. Examples of delimiters are brackets, parentheses, braces, single or double quotes etc. You can also customize this code to extract the string between two special characters or two sub strings. Comments on each line of code explains you to easily understand the code. Note: […]