Excel VBA - Access-Excel.Tips

Excel VBA usedRange Property and reset usedRange

December 22, 2015March 13, 2015 by Wyman W

This tutorial explains how to use Excel VBA usedRange Property to find the last used row and column number, and reset usedRange.

You may also want to read:

Excel VBA custom Function last row and last column

Excel delete blank rows and blank cells before import into Access

Excel VBA UsedRange Property

Excel VBA UsedRange is a worksheet Property, it returns the area Range bounded by first used cell and last used cell. “Used Cell” is defined as Cell containing formula, formatting, value that has ever been used, even though the value was deleted. In Excel worksheet, we can press Excel shortcut key CTRL+END to select the last used cell.

The picture below highlights the area of UsedRange

Reset UsedRange

UsedRange can be reset using the following Procedure.

Public Sub reset_usedrange()
    a = ActiveSheet.UsedRange.Rows.Count
End Sub

The above procedure work under normal circumstances, but it does not work in some cases where the data source was downloaded from other system. In that case you should use the below procedure to clear all empty cells and then delete the row.

Public Sub delete_empty_row()
    Application.ScreenUpdating = False

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub

Find the last used row and column

A popular use of UsedRange Property is to find the last used row and column.

To find the last row number, use the below code

usedRangeLastRow = Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

To find the last column number, use the below code

usedRangeLastColNum = Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

To convert column number to column letter

usedRangeLastColNm= Split(Cells(1,Activesheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column).Address, "$")(1)

Find the address of usedRange

Address Property returns the absolute address of a Range. In the above example, the below code will return $B$1:$E$4

Activesheet.UsedRange.Address

Afterwards you can use split function to get the starting or ending Range.

Click here to see how to use Split Function.

Outbound References

https://msdn.microsoft.com/en-us/library/office/aa207501%28v=office.11%29.aspx

Excel VBA Workbooks.Add Method to create new workbook

March 5, 2015February 4, 2015 by Wyman W

This page explains Excel VBA Workbooks.Add Method, illustrate how to create new workbook in Excel VBA or move data from one workbook to a new workbook

What does Excel VBA Workbooks.Add Method do?

Excel VBA Workbooks.Add Method is used to create new workbook. Suppose you have a workbook called workbook1, inside workbook1 you need to create new workbook and manipulate it, then you need to insert the Workbooks.Add Method in workbook1. After you have created the workbook, the workbook becomes activated, you can set a workbook title, save file, etc.

One common use of Workbooks.Add Method to copy data from a workbook to new worksheets in order to distribute different worksheets to different people.

Syntax of Workbooks.Add Method

Workbooks.Add(Template as Object)

Template

Optional Object. Determines how the new workbook is created. If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template. If this argument is a constant, the new workbook contains a single sheet of the specified type.Can be one of the following XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet.

If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property). If the Template argument specifies a file, the file name can include a path.

Remarks

Workbooks.Add is a Method (a Function of an Object), it returns a Workbook Object. You can visually see a workbook opened after using the Method, and you can also define a workbook Object to get the returned Workbook.

Set NewBook = Workbooks.Add

Example 1 – Create new workbook and close it

The below code create a workbook called “New workbook”, and then set the Title and Subject for the workbook, finally save the workbook and close it. As soon as the newly created workbook is closed, the original workbook becomes activated again.

Public Sub createWB()
    Set newbook = Workbooks.Add
    With newbook
        .Title = "This title is displayed in Info > Properties"
        .Subject = "This subject is displayed in Info > Properties"
        .SaveAs Filename:="C:\Users\WYMAN\Desktop\New workbook.xlsx"
        .Close
    End With
End Sub

Example 2 – Move specific data to new workbook

This was a question originally asked in Microsoft Community and answered by me.

Question

I’m sure my issue is not unique. I have a excel document with hundreds of columns and only want about a dozen of them. I need to be able to extract specific
columns to a new excel sheet as a repeated process without manual intervention.
All I need is to pull certain columns into a new excel sheet from an excel document that is updated daily.
Do we have an automated process where I can just run a macro and pull the updated data from an excel document into a new one?
Any help is greatly appreciated.
Thank you.

Answer

The below code extracts specific columns in a workbook and then copy to a new workbook.

Public Sub extractCol()
     Set range1 = Range("A:D, BI:BI, BQ:BQ,CL:CL,CM:CN,CT:CT,DB:DB")
     range1.Copy
     Set newbook = Workbooks.Add
     ActiveCell.PasteSpecial Paste:=xlPasteValues
 End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.add.aspx

Excel VBA Formula Property of Range

May 19, 2016January 8, 2015 by Wyman W

This Excel VBA tutorial explains how to use Formula Property in Excel VBA.

Formula Property in Excel VBA

There are two functions for Formula Property:

1. To set formula in a Range, like the way you type a formula in worksheet

2. To retrieve formula from a Range

Syntax of Formula Property

To set a formula:

Range.Formula = "=your formula"

To retrieve a formula:

variableName = Range.Formula

Remarks

You can also set a formula using Value Property

Range.Value = “=your formula”

But the difference is that you cannot retrieve formula using Value Property

You can also set a formula using R1C1 Reference Style, see the below article

Excel VBA FormulaR1C1 Property of Range

Example of using Formula Property

Line	VBA code	Result	Explanation
1	Range(“C2”).Formula= “=1+2”	A value “3” is shown in Range C2	To set a formula for C2 as =1+2
2	Msgbox(Range(“C2”).Formula)	=1+2	To retrieve formula from C2
3	Msgbox(Range(“C2”).value)	3	To retrieve value from C2
4	Range(C3″).value = “=1+2”	A value “3” is shown in Range C3	To set a formula for C3 as =1+2

Outbound References

http://msdn.microsoft.com/en-us/library/office/ff838835%28v=office.15%29.aspx

Excel VBA delete worksheet based on worksheet name

December 25, 2014December 18, 2014 by Wyman W

Solution to delete worksheet based on worksheet name

I am providing the solution for the “Deluxe-Plus” version in the below code

To determine which worksheet is to delete, create an array called delWS(), and then loop through all the worksheet name to find the worksheet name that contains “DELETE” or “OLD”.

If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0

Instr function is to check whether the worksheet name contains the key words, the function returns the starting position of the key word in the worksheet name; return 0 if not found.

Application.DisplayAlerts = False is used to prevent alert message before deleting worksheet.

Public Sub deluxPlus()
    Dim delWS() As Variant
    Dim WS As Worksheet
    'To store all the unwanted worksheets in array delWS()
    For Each WS In ThisWorkbook.Worksheets
       If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0 Then
           Count = Count + 1
           ReDim Preserve delWS(Count)
           delWS(Count) = WS.Name
       End If
    Next WS
    'To save all the unwanted worksheets name in variable alldelWS
    If Count > 0 Then
       For i = 1 To Count
           alldelWS = alldelWS & "," & delWS(i)
       Next i
    End If
    alldelWS = Right(alldelWS, Len(alldelWS) - 1)
    'To delete all worksheets in array delWS()
    If Count > 0 Then
       x = MsgBox("Are you sure you wanna delete them? " & vbNewLine & alldelWS, vbOKCancel)
       If x = vbOK Then
          For i = 1 To Count
              Application.DisplayAlerts = False
              Worksheets(delWS(i)).Delete
              Application.DisplayAlerts = True
          Next i
       End If
    End If
 End Sub