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