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 Macro Programming Tutorials
This blog is about programming in Visual Basic For Applications (Excel VBA Macro programming), Excel VBA Macro tutorial, and Excel VBA Macro examples.
Saturday, November 7, 2015
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.
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:
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
Sunday, December 27, 2009
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".
The following example is more efficient than previous example:
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:
If you like posts in this blog, you can to support me :)
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 :)
Saturday, December 12, 2009
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)
Settings
The following example add two months to October 31, 2009:
Related posts:
---
If you like posts in this blog, you can to support me :)
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. |
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 :)
Wednesday, March 18, 2009
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:
Related posts:
---
If you like posts in this blog, you can to support me :)
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 :)
Subscribe to:
Posts (Atom)