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

Useful VBA routines in Microsoft Excel (Part 1)

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

We have spoken to previous article what is the VBA (Visual Basic for Applications) and what possibilities it has within Microsoft Excel.

In this article we will look at some routines I have written that can be executed by calling them on a button or after an event such as when the file is opened.

Save to a new Excel file

With the following routine we have the possibility to save an Excel file in another new one.

The parameters:

  • Filename:= we define the file name with Format(Now()) we have the possibility to add the current date.
  • ActiveWorkbook.Close enables us to close the Excel file after saving the PDF.
  • Application.Quit it closes the original Excel file without saving its changes

Sub filesave()

Application.DisplayAlerts = False
Dim FileName As String
FileName = ActiveWorkbook.Path & "\arxeio_"
ActiveWorkbook.SaveAs (FileName & Format(Now(), "yyyyMMdd") & ".xlsx"), FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

ActiveWorkbook.Close False
Application.Quit
Application.DisplayAlerts = True
End Sub

Save Excel to PDF

With the following routine we have the ability to save an Excel file to PDF.

The parameters:

  • .Orientation we define whether the file will be Portrait or Landscape
  • .PrintArea we define our frame, i.e. the most part we want from Excel to appear in the PDF.
  • Filename:= we define the file name with Format(Now()) we have the possibility to add the current date.
  • ActiveWorkbook.Close enables us to close the Excel file after saving the PDF.

Sub savepdf()

Application.DisplayAlerts = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PrintArea = "$A$1:$G$100"
.PrintTitleRows = ActiveSheet.Rows(2).Address
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
("C:\Folder\arxeio_" & Format(Now(), "yyyyMMdd") & ".pdf"), Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=True, OpenAfterPublish:=False

ActiveWorkbook.Close False
Application.DisplayAlerts = True

Delete column

With the following routine we can delete from a sheet, in this case "sheet1" a specific column, e.g. the "H".

Sub deletecolumn()

ActiveWorkbook.Sheets("sheet1").Range("H:H").EntireColumn.Delete

End Sub

Delete sheet

With the following routine we can delete sheets from Excel that we no longer need, simply by setting their names.

Sub sheetdel()

Application.DisplayAlerts = False
ActiveWorkbook.Sheets("sheet2").Delete
ActiveWorkbook.Sheets("sheet3").Delete
ActiveWorkbook.Sheets("sheet4").Delete
Application.DisplayAlerts = True

End Sub

Convert cells to simple values

Many times in Excel we use functions such as e.g. the SUM, but these are dynamic which means that if the data changes they will change as well. So when we want to keep the values frozen by eliminating the functions from which they got their value, we can do it with the following routine.

Sub ValuesOnly()

Dim ws As Worksheet    
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    
End Sub

Delete button

If we want to delete the button with which we call the VBA actions we can do it with the following VBA routine.

Sub deletebutton()

ActiveSheet.Shapes("onoma_koubiou").Delete

End Sub

Copy the content with padding across the column

In case we want to copy a cell that may contain a function or some text in the entire column, for example from cell "A2" to "A1000" as in the example, it is done with the following routine.

Sub filldown()

Worksheets("sheet1").Range("A2:A1000").filldown

End Sub

Delete rows that have a specific value in some column

To delete any rows that contain a certain value as in our example any rows that have the value 0 in column “D” in the sheet “sheet1” we use the routine below.

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 DeleteERows()

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("D3", "D1000")
DeleteStr = 0
For Each rng In InputRng
    If rng.Value = DeleteStr 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

How are they performed?

When we have the code passed to Excel as we saw in the link from the previous article, 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.

Private Sub Koumpi_Click()

Call deletebutton
Call savepdf
Call filesave

End Sub

This was the first part VBA routines, there will be a next article with several more.

Share it

Leave a reply