Yes/No
There are times you may want users to click Yes or No. Just insert this line of code. Here the Select Case statement is used.
YesNo = MsgBox("This macro will ... Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
'Insert your code here if Yes is clicked
Case vbNo
'Insert your code here if No is clicked
End Select
Counting Rows & Columns & Sheets
When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.
Sub Count()
myCount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox myCount
End Sub
Close all Files
Sometimes you may want to close all files without saving. Doing it manually is a hassle with the question "Do you wanna save?"
Sub CloseAll()
Application.DisplayAlerts = False
myTotal = Workbooks.Count
For i = 1 To myTotal
ActiveWorkbook.Close
Next i
End Sub
Current Cell Content
Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example a message box is displayed. Replace this with a macro should you require another course of action.
Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox "Text" 'replace this line with your macro
Else
If ActiveCell = "" Then
MsgBox "Blank cell" 'replace this line with your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox "formula" 'replace this line with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox "date" 'replace this line with your macro
Else
End If
End If
End Sub
Deleting Empty Rows
To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
Deleting Range Names
To delete all the range names in your workbook, this macro will do the trick.
Sub DeleteNames()
Dim NameX As Name
For Each NameX In Names
ActiveWorkbook.Names(NameX.Name).Delete
Next NameX
End Sub
Current Cell Position
Sometimes we need to know the current cell position. This would do the trick.
Sub MyPosition()
myRow = ActiveCell.Row
myCol = ActiveCell.Column
Msgbox myRow & "," & myCol
End Sub
