Excel VBA: How To Delete Entire Row Or Entire Column

On previous post, I've explained how to delete cells shift to left and up, now i will explain how to delete entire row or entire column:

To delete entire row:

Sub deleteShiftLeft()
    Thisworkbook.Activesheet.Range("B10").EntireRow.Delete
End Sub

To delete entire column:

Sub deleteShiftUp()
    Thisworkbook.Activesheet.Range("B10").EntireColumn.Delete
End Sub

That's all. Fin.

Excel VBA: How To Delete Cell Shift Up and Shift Left

It's very simple to delete cell using VBA, we can use Delete method. This is an example how to do it:

Delete cell shift to left:

Sub deleteShiftLeft()
    Thisworkbook.Activesheet.Range("B10").Delete Shift:=xlToLeft
End Sub

Delete cell shift up:

Sub deleteShiftUp()
    Thisworkbook.Activesheet.Range("B10").Delete Shift:=xlUp
End Sub

Fin.

Excel VBA: How to check whether a sheet exist or not

Sometimes we need to check whether a sheet exist or not to avoid run time error. Below is a simple function to do that task:

Function isSheetExist(ByVal workbookName As String, ByVal sheetName As String) As Boolean
Dim ws As Worksheet, ss
Dim vbR As Boolean
   
    vbR = False
   
    Set ss = Workbooks(workbookName).Worksheets
    For Each ws In ss
        If (ws.Name = sheetName) Then
            vbR = True
            Exit For
        End If
    Next ws
    isSheetExist = vbR
End Function

And this is an example to use that function: 

Sub FunctionTest()
 If isSheetExist(ThisWorkbook.Name, "Sheet1") = False Then
     MsgBox "Sheet is not exist."
 Else
     MsgBox "Sheet is exist."
 End If
End Sub

I hope this example help you.

Excel VBA: Connect to Microsoft Access Database (MDB) using ADODB

First, you have to enable Microsoft ActiveX Data Object Library. Follow this instruction:
1. Open Visual Basic for Application, click Tools, then click Add References

2. Search for   Microsoft ActiveX Data Object Library

This is an example code to connect to ADODB:

Private Sub CommandButton1_Click()
Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset

    adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb; Persist Security Info=False"
    adoConn.Open

    adoRS.Open "SELECT COUNT([employee_id]) AS empID FROM [Table1]", adoConn, adOpenKeyset, adLockOptimistic
   
    ActiveSheet.Range("A1").Value = adoRS!empID   
    If adoRS.State = adStateOpen Then adoRS.Close
    If adoConn.State = adStateOpen Then adoConn.Close
   
End Sub

Excel VBA: Find And Replace String Using VBA Code

There are several ways to replace string using VBA code,  by looping each cell or by using VBA Replace function.

Let's say that we want to replace "Macrosoft Excel" with "Microsoft Excel" from A1 through A500.

This first example loops from A1 through A500 and replace "Macrosoft Excel" with "Microsoft Excel".

Sub Find_Replace1()
    Dim I As Integer
    Dim SFind As String
    Dim SReplace As String
    
    SFind = "Macrosoft Excel"
    SReplace = "Microsoft Excel"
    For I = 1 To 500
        If Cells(I, 1).Value = SFind Then
            Cells(I, 1).Value = SReplace
        End If
    Next I
End Sub

The following example is more efficient than previous example:

Sub Find_Replace2()
    Dim SFind As String
    Dim SReplace As String
    
    SFind = "Macrosoft Excel"
    SReplace = "Microsoft Excel"

    Range("A1:A500").Replace _
        What:=SFind, Replacement:=SReplace, _
    LookAt:=xlWhole, MatchCase:=False
End Sub

If you to make the search case sensitive you can change the MatchCase property to true. And also if you want to replace data that contain part of the searched data you can change the LookAt property to xlPart.

Related posts:

• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Date Calculations

To add or subtract an interval (a relative date) from a date, we could use DateAdd function. The syntax is:

DateAdd(interval, number, date)

The DateAdd function syntax has these named arguments:

Part	Description
interval	Required. String expression that is the interval of time you want to add.
number	Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
date	Required. Variant (Date) or literal representing date to which the interval is added.

Settings

The interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

The following example add two months to October 31, 2009:

MsgBox DateAdd("m",2,"31-Jan-09"))

Related posts:

• The MsgBox Function
  
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Determining whether a path exists

To check whether a path exists or not, we can use Excel VBA's GetAttr function. The following function returns True if a specified path exists, and return False otherwise:

Function isPathExist(ByVal pathname As String) As Boolean
 On Error Resume Next
 isPathExist = GetAttr(pathname) And vbDirectory = vbDirectory
End Function

Related posts:

• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution
• Determinig whether a file exists

---
If you like posts in this blog, you can to support me :)

Excel VBA: Determining Whether A File Exists Or Not

There are several ways to check whether a file exists. By using Microsoft Excel VBA statements and functions, or by using FileSystemObject (Microsoft Scripting Library). The following function returns True if a particular file exist, and return False if file does'nt exist. This function uses Dir function to check whether a file exists or not.

Function isFileExist(ByVal fname As String) As Boolean
 isFileExist = False
 If Dir(fname) <> "" Then isFileExist = True
End Function

Here's example how to use the function above:

Sub FunctionTest()
 If isFileExist("D:\SomeFile.txt") = False Then
     MsgBox "File not exists."
 Else
     MsgBox "File already exist."
 End If
End Sub

The next function do exactly as previous function, but this function uses FileSystemObject to check whether a file exist:

Function isFileExist2(ByVal fname As String) As Boolean
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    isFileExist2 = fs.FileExists(fname)
End Function

FIN.

Related posts:

• Excel VBA Macro Examples: Sub Procedure
• The MsgBox Function
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Font Formatting

When we working in Excel, if we want to changes font properties we may use Font tab in the Format Cells dialog box. In Excel VBA, we control the font's appearance by the Font object. The Font object have several properties that correspond to various aspects of the font's appearance. Here are several list of font's properties:

Property	Description
Name	The name of the font.
Bold	True if the font is bold. Read/write Variant.
Italic	True if the font style is italic. Read/write Boolean.
Underline	Returns or sets the type of underline applied to the font. Can be set to xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting. Read/write Variant.
Size	Returns or sets the size of the font. Read/write Variant.
Subscript	True if the font is formatted as subscript. False by default. Read/write Variant.
Superscript	True if the font is formatted as superscript; False by default. Read/write Variant.
Strikethrough	True if the font is struck through with a horizontal line. Read/write Boolean.

This example sets font name of range A1:B4 to Tahoma in ActiveSheet in ActiveWorkbook:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Name = "Tahoma"

The Color property uses an RGB value, which identifies a color in terms of its red, green,
and blue components. To set this property, use the RGB function:

RGB(r, g, b)

The next example sets font color of range B4 to Blue in ActiveSheet in ActiveWorkbook:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Color = RGB(0, 0, 255)

We can also use predefined constants to sets font color, they are vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and vbWhite.

This example do exactly as previous example:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Color = vbBlue


Related Post:

• Number Formatting In Excel VBA Macro
• Changing Row And Column Size

---
If you like posts in this blog, you can to support me :)

Excel VBA: Changing Row and Column Size

Sometimes, in Microsoft Excel we need to change width of columns or height of rows in a worksheet to accomodate data they contain. In Excel Visual Basic for Application (VBA macro), to change columns width we can use ColumnWidth property. Following excel macro code sets width of column C in "Sheet1" worksheet to 24:

Sheets("Sheet1").Columns("C").ColumnWidth = 24

To change columns width to fit data in columns, we can use AutoFit method. The following example uses AutoFit method to change the size of C:F in the "Sheet1" worksheet:

Sheets("Sheet1").Columns("C:F").AutoFit

RowHeight property is used to change rows height of a range. For example:

Sheets("Sheet1").Rows(2).RowHeight = 56


Related Post:

• Number Formatting In Excel VBA Macro

---
If you like posts in this blog, you can to support me :)

Excel VBA: Number Formatting in Excel VBA Macro

Number formatting controls how numbers on cells are displayed, it has no effect on cells that contain text. In Microsoft Excel, if we want to apply number formatting we can use Format Cells dialog box. To format numbers in VBA macro we can use the NumberFormat property. Following are some number formatting codes to format numbers.

Number Formatting Codes

Character	Meaning	Code example	Format example
#	Significant digit	##.#	10.78 displays as 10.9
0	Nonsignificant 0	#.00	5.4 displays as 5.40
.	Decimal point	##.##	14.55 displays as 14.55
$	Currency symbol	$#.##	56.78 displays as $56.78
%	Percent symbol	#.#%	0.075 displays as 7.5%
,	Thousands separator	#,###	123000 displays as 123,000

Here is the example VBA code to display numbers with no commas or special characters, and no decimal places:

Sub NumFormat()
 Range("A1").NumberFormat = "0"
End Sub

We can display positive and negative numbers differently. Following number format code will display negative numbers in red color.

#.##;[Red]#.##

To specify a display color, include the color name enclosed in square brackets at the start the format code. The available color names are:

• Black
• Blue
• Cyan
• Green
• Magenta
• Red
• White
• Yellow

---
If you like posts in this blog, you can to support me :)

Excel VBA Macro: Creating a Chart

In this post, we will create a chart using Microsoft Excel VBA macro.

Embedded Chart

Use an embedded chart when you want the chart displayed as part of a worksheet along with the data and/or other charts. Here is the example to create an embedded chart:

Public Sub EmbeddedChart()
 Dim myChartObject As ChartObject
 Dim myChart As Chart
 Set myChartObject = Worksheets("Sheet1").ChartObjects.Add(100, 150, 300, 225)
 Set myChart = co.Chart
 myChart.SetSourceData Source:=Worksheets("Sheet1").Range("A2:E6"), PlotBy:=xlRows
End Sub

Chart Sheets

Use a Chart Sheet when you want a chart displayed in different sheet.

Public Sub ChartSheet()
  Dim mychart As Chart
  Set mychart = ActiveWorkbook.Charts.Add
  mychart.SetSourceData Source:=Worksheets("Sheet1").Range("A2:E6"), PlotBy:=xlRows
End Sub

Related posts:

• Excel VBA Macro Examples: Sub Procedure

---
If you like posts in this blog, you can to support me :)

Excel VBA: Selecting A Row Or Column

To select the entire column we can use the EntireColumn property. The following excel VBA macro example demonstrates how to select the column of the active cell.

Sub SelectColumn()
ActiveCell.EntireColumn.Select
End Sub

The next following excel VBA macro example demonstrates how to perform an operation on all cells in the selected row. This following procedure changes all cells font size to 18 in the row that contains the active cell.

Sub ChangeFontSize()
 ActiveCell.EntireRow.Font.Size = 18
End Sub

Related posts:

• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects

---
If you like posts in this blog, you can to support me :)

Excel VBA: Runs An Executable Program In Excel VBA Macro

Sometimes, it's very useful to run another application from Microsoft Excel. To runs another application in Microsoft Excel, we can use VBA's Shell function. The following example, will lunch the Notepad application and if the Shell function failed to launch the application, it generates an error.

Sub ExecNotepad()
  On Error Resume Next
  AppVal = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)
  If Err <> 0 Then
      MsgBox "Can't start the application.", vbCritical, "Error"
  End If
End Sub

Related posts:

• The InputBox Function
• The MsgBox Function
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)

Excel VBA: Looping through a range

The following microsoft excel VBA macro example demonstrates how to loop through all the cells in a range. This example uses the For Each...Next statement to search the word "Microsoft Excel VBA" in a range and changes its font style to bold. In this case, the range is from A1 to E5.

Sub ChangeFontStyle()
   Dim Cell As Range
  
   For Each Cell In Range("A1:E5")
       If LCase(Cell.Value) = "microsoft excel vba" Then
           Cell.Font.Bold = True
       End If
   Next Cell
End Sub

Related posts:

• Excel VBA Macro Tutorial: Looping
• Find A Particular Text Within Ranges And Return Its Row Position
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Range Objects
• Excel VBA Macro Tutorial: Controlling Execution

Excel VBA: Cut and paste using macro

In this Excel VBA Macro Examples section, I will show you how to move a range. This following example will move a range A4:E4 to I15:M15 in active sheet.

Sub MoveRange()
  ActiveSheet.Range("A4:E4").Cut _
    Destination:=ActiveSheet.Range("I15")
End Sub

If Destination argument is omitted, Microsoft Excel cuts the range to the Clipboard.

Related posts:

• Excel VBA Macro Examples: Copying a range
• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution