Closure
Closure
Home » Business Intelligence » Microsoft Excel » Useful VBA routines in Microsoft Excel (Part 2)

Useful VBA routines in Microsoft Excel (Part 2)

Useful VBA routines in Microsoft Excel (Part 2)
, , , Leave a comment
Useful VBA routines in Microsoft Excel (Part 2)
Latest posts by Stratos Matzouranis (see all)

Following the previous article we'll see some more routines VBA (Visual Basic for Applications) that we have the ability to use at Microsoft Excel.

These routines can be executed by calling them on a button or after an event such as when the file is opened.

Delete all rows that have a blank cell

With the following routine we can delete as many lines as the cell in a particular column has a blank space. E.g. Column “A” in sheet “sheet1”:

Sub DeleteERowsOther()

    Sheets("sheet1").Select
    Range("A3:A1000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Run cmd (command prompt) / Putty commands through Excel

With the following routine we have the possibility to execute a file with commands or to run commands directly. In the example we call plink.cmd(Putty) and then call to save a Google ping to a folder. With the /k parameter after execution the window will remain open:

Sub callcmd()

Application.DisplayAlerts = False
Dim FileName As String

FileName = ActiveWorkbook.Path & "\plink.cmd /c "  'gia ektelesi etoimou arxeiou
Shell FileName

Shell "cmd.exe /c ping google.com" & " && cd\Users\Username\Desktop" & " &&mkdir tester"   'gia apeutheias cmd commands
'me /k  to parathiro cmd den tha kleisei

 Application.DisplayAlerts = True
End Sub

Direct execution of cmd file (command prompt) through Excel

A simpler approach to the previous routine to directly execute a ready-made cmd file. The file should just be in the same path as Excel and all we need to parameterize is the name in the parameter shellCommand:

Sub runbat()

Application.DisplayAlerts = False
Dim folderPath As String
Dim shellCommand As String

folderPath = Application.ActiveWorkbook.Path

shellCommand = """" & folderPath & "\" & "Arxeio.bat" & """"

Call Shell(shellCommand, vbNormalFocus)
End Sub

Import text file into Excel cells

With the following routine we can insert a txt file into an Excel sheet.

The parameters:

  • Filename:= we define the name of the file.
  • Set wsI = wbI.Sheets(“…”) the name of the sheet where the data will be placed.
  • Set wbO = Workbooks.Open(FileName, Format:=3) what delimiter will we use with 3 being space and 4 being semicolon.

Sub importtxt()

   Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet
    Dim FileName As String
    FileName = ActiveWorkbook.Path & "\apotelesmata.txt"
    Set wbI = ThisWorkbook
    Set wsI = wbI.Sheets("apotelesmata") '<~~ To fyllo p tha topothetithoun
    wsI.Range("A1:AT1000").Clear
        '3 = space 4 = semicolon
    Set wbO = Workbooks.Open(FileName, Format:=3)
    wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False

End Sub

Freeze execution of the following routines for x time

With the following routine we can freeze the execution of the following routines for x time. We may want a reasonable amount of time for the previous one to be completed. E.g. as in the example 30 seconds:

Sub WaitTimer()

 Application.DisplayAlerts = False

Application.Wait (Now + TimeValue("0:00:30"))

Application.DisplayAlerts = True
End Sub

Delete rows that have an empty value in some column

We have seen a similar routine that works when in a column we have a specific value. The following routine is made for the case that the value is empty

The parameters:

  • Sheets(“…”).Select we define the friend that concerns us.
  • Set InputRng = Range(….) we define where the value we want to check will be located.
  • DeleteStr we define the price we are looking for.

Sub deleteifcolumnisEmpty()

Application.DisplayAlerts = False
Sheets("sheet1").Select
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Set InputRng = Range("g1", "g10000")

For Each rng In InputRng
    If Len(rng.Value) = 0 Then
        If DeleteRng Is Nothing Then
            Set DeleteRng = rng
        Else
            Set DeleteRng = Application.Union(DeleteRng, rng)
     End If
    End If
Next

DeleteRng.EntireRow.Delete
Application.DisplayAlerts = True
End Sub

Copy data from one sheet to another

With the following routine we can copy the data from one sheet to another and then delete the original sheet:

Sub copyToanotherSheet()

Application.DisplayAlerts = False
Worksheets("TEMP_sheet").Range("A1:AA10000").Copy Destination:=Worksheets("sheet").Range("A5:AA10005")
Worksheets("TEMP_sheet").Delete

Application.DisplayAlerts = True
End Sub

Compare two values and display a pop-up window

We can compare prices and if it does not meet the conditions a pop-up window will appear with a message.

The parameters:

  • Set Rng1 = Range(“…”) we call the value of the cell.
  • Value we set the value that we check if it is valid.
  • Prompt we set the text in the window.
  • Title we set the title of the window.

Sub Worksheet_Calculate()

Dim Rng1            As Range
Dim Value           As String
Dim Prompt          As String
Dim Title           As String

    Set Rng1 = Range("B1")
    Value = "Swsto"
    Prompt = "Lathos Katametrisi"
    Title = "Error"

    If Rng1.Value = Value Then
        MsgBox Prompt, vbInformation, Title
        End
    End If

End Sub

How are they performed?

As we mentioned in the first part, when we will have the code passed to Excel, we call them by adding an event to the code, e.g. when opening Excel or we call them through the code of a button when it is clicked.

Share it

Leave a reply