VBA To Enlarge Picture

The Code:

Option Explicit
Dim fd As Boolean
Sub EnlargePicture()
Call Picture_Click
End Sub
Sub Picture_Click()
Dim i, j As Long
Dim sh As Shape
i = 200
j = 300
Set sh = ActiveSheet.Shapes(Application.Caller)
fd = fd Xor True
    With sh
        If fd Then
            .Width = i
            .Height = j
        Else
            .Width = .TopLeftCell.ColumnWidth
            .Height = .TopLeftCell.RowHeight
        End If
    End With
End Sub

Note:

1. Adjust i and j according to requirements

How it works:

1. It will enlarge the picture when you click on it
2. It will resize to original size when you re-click on it

VBA To Delete Last Row

This is also related to below queries:

1. excel vba delete last row in table
2. vba select last row in table
3. select last row vba
4. excel vba find last row with data
5. excel vba to delete grand total row

The Code:

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.EntireRow.Select
Selection.EntireRow.Delete

Note:

1. Normally this function is used to delete last row which consists of Grand Total

VBA Function To Combine Range

The Code:

Dim rng As Range
Dim rngA As Range
Dim rngB As Range
Set rngA = ActiveSheet.Range("A3:V3")
Set rngB = ActiveSheet.AutoFilter.Range
Set rng = Application.Union(rngA, rngB)

Where:

1. Column size for rngA = rngB
2. A3:V3 is the header of the autofilter

Note:

1. Normally the function is used to insert the range in email as a body

VBA Function To Check If File is Exists

This is also related to below queries:

1. vba check if file exists in folder
2. vba if file exists then delete
3. check if file exists vb
4. fileexists vba
5. excel vba check if file is open

The Code:

If Dir("Directory") <> "" Then

Where:

Directory = complete path of the file

Note:

1. Normally, we are using this to check if the same file name already existed or not before saving or replacing the file
2. This is just to be cautious 
3. Next action is depending on your requirement. You could copy, open, delete, or replace the file

VBA Function To Check If File Is Opened

This is also related to below queries:

1. excel vba check if workbook is open
2. isfileopen vba
3. excel vba check if workbook is open by another user
4. vba check if workbook is open and if not open it
5. isworkbookopen vba
6. excel vba check if workbook open then close

The Code:

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next
    filenum = FreeFile()
    Open filename For Input Lock Read As #filenum
    Close filenum
    errnum = Err
    On Error GoTo 0
    Select Case errnum
        Case 0
         IsFileOpen = False
        Case 70
            IsFileOpen = True
        Case Else
 
    End Select
End Function
If Not IsFileOpen("FileDirectoryToOpen") Then
    Workbooks.Open "FileDirectoryToOpen"
End If

Where:

FileDirectoryToOpen = complete path if your file name. The file must consist of file extension. Example: For Excel, must ended with .XLSX

VBA Function To AutoFill Down

This is also related to below queries:

1. vba autofill to last row
2. vba autofill formula
3. excel vba autofill dynamic range
4. autofill vba variable range
5. excel vba autofill down
6. selection.autofill destination to last row
7. vba autofill multiple columns

The Code:

Dim i As Long
Application.ScreenUpdating = False
i = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range("A2:A" & i)
Range("A2:A" & i).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Where:

i = number of rows to autofill

Note:

1. Range("A2:A" & i). Begins with A2. Change :A" to your desired column if you have multiple cells
2. The operation will perform autofill then copy the autofill range/ areas and then copy paste special values. You may remove this if you wish to keep the formula

VBA To Count Total Rows or Visible Rows

This is also related to below queries:

1. excel vba count visible rows in table
2. specialcells xlcelltypevisible rows count
3. vba loop through visible rows
4. specialcells(xlcelltypevisible).rows.count not working
5. countif visible cells vba
6. get row number of filtered row vba

The Code:

---

Dim i As Long
i = Cells(Rows.Count, "A").End(xlUp).Row
''' VBA To count Total Rows in Excel

Dim j As Long
j = Sheets("Sheet1").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
'''VBA to count number of visible rows after autofilter row

---

Note:

1. Take column A or first column to count as first column is normally clean. It does not contain any blank rows
2. For Autofilter, we need to minus 1 as header is also counted in the formula. Minus 1 will remove header. If you need to count the header then no need to Minus 1