Excel VBA – find out the position of your last row, column & column name…

This is a very useful function to me, it helps to find out the position of the last row and column in your worksheet, and will also get the column name for calculation and automation.  It is also hard to find out and fix the boundary in a worksheet when you are performing automatic calculation and move.

You can refer to my previous “User defined type”, to create your own variable first, so that this function will work properly.

https://wemakeitx.wordpress.com/2017/03/11/excel-vba-how-to-create-user-defined-type/

‘===================================================================================
‘ Find out the LAST row, column, and return the values (row, column & column name)
‘===================================================================================

Function FindLastRowColumn() As RowColumn

Dim infoRowColumn As RowColumn
Dim iCol As Integer
Dim iRow As Long
Dim lastColumn As Integer
Dim lastRow As Long
Dim MBox As Integer

iCol = ActiveSheet.UsedRange.Column – 1 + ActiveSheet.UsedRange.Columns.Count
lastColumn = iCol

iRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count
lastRow = iRow

ColumnName = ConvertToLetter(lastColumn)
‘MBox = MsgBox(“Column: ” & LastColumn & “(” & ColumnName & “)” & vbCrLf & “Row: ” & LastRow, vbOKOnly)

infoRowColumn.xRow = lastRow
infoRowColumn.xColumn = lastColumn
infoRowColumn.xColumnName = ColumnName

FindLastRowColumn = infoRowColumn

End Function

 

Advertisements

Excel VBA – Check if your worksheet exists…

This is a function to check if the worksheet you are looking or working exists.

Function IsWorksheetExist(ByVal WSName As String) As Boolean

Dim CheckWS As Worksheet

IsWorksheetExist = False

For Each CheckWS In ActiveWorkbook.Worksheets
If CheckWS.Name = WSName Then IsWorksheetExist = True
Next CheckWS

End Function

Excel VBA – Check if your workbook exist or not?

Before opening your workbook, you will need to verify if the workbook exists or not, so that it won’t return error or the VBA will stop improperly.

If the workbook exists, it will return “TRUE“.

If the workbook doesn’t exist, it will return “FALSE“.

Function DoesWorkBookExist(ByVal wbPath As String, ByVal WBName As String) As Boolean

Dim i As Integer
Dim FileCheck As String

FileCheck = wbPath & “\” & WBName

If Dir(FileCheck) <> “” Then
‘MsgBox (“Workbook – ” & wbName & ” exists”)
DoesWorkBookExist = True
Else
‘MsgBox (“Workbook – ” & wbName & ” doesn’t exist”)
DoesWorkBookExist = False
End If

End Function

Excel VBA – Text to Columns feature

It is a subroutine and can be executed directly via the Macro list.  The function is to modify delimited text into separate columns, so you can manipulate data better and easily.

objRange1 is the definition of the range within a worksheet, you can specify.

Destination:=Range(“A3”) is the location where the output will be appeared, you can specify.

Sub Split2Col()

Dim objRange1 As Range

‘Set up the ranges
Set objRange1 = Range(“A1:A20”)

‘Do the first parse
objRange1.TextToColumns _
Destination:=Range(“A3”), _
DataType:=xlDelimited, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=”|”

End Sub

Excel VBA – How to convert Column Number into Column Letter?

This is a function, to convert the Column in number into the Column Letter, in order to work with most Range, functions in VBA, the Column Letter will be very useful than the Column number.

e.g. Column “A” = Column number 1

Column “C” = Column number 3

Function ConvertToLetter(ByVal iCol As Integer) As String

If iCol <= 26 Then
‘ Columns A-Z
ConvertToLetter = Chr(iCol + 64)
End If

If iCol > 26 Then
‘ Column label greater than Z, going into AA…
ConvertToLetter = Chr(Int((iCol – 1) / 26) + 64) & Chr(((iCol – 1) Mod 26) + 65)
End If

End Function

Excel VBA – How to find out workbooks’ path or location?

This is a custom function in Excel VBA, where you can find out the path or location of your workbook; it returns the information as a text output and you can make use of this for further programming,

Function DetectPath(ByVal WBName As String) As String
DetectPath = Workbooks(WBName).Path

End Function

Excel VBA – How to delete entire row(s)?

This is a custom function, to delete certain row(s) based on your specified Column and Row, then choose to delete the subsequent rows together in one go.

Function DeleteRows(ByVal validColumn As String, ByVal StartRow As Long, ByVal NumOfRows As Long)

Dim i As Long
Dim DelString As String

DelString = validColumn & StartRow & “:” & validColumn & NumOfRows

Range(DelString).EntireRow.Delete

End Function

e.g.
OpenResult = DeleteRows(“A”, 1, 9)