The content of the article
Almost all office workers are faced with tasks that are associated with processing large amounts of data or routine things when you have to copy and paste a large stream of information by hand. In such minutes it seems that you are wasting hours, and the “machine” would probably have coped with the routine in a second. We are taking a decisive step towards automating the routine.
By the way, we previously wrote about how much time can be saved by putting in the effort and recording a macro.
How to create a macro in VBA / Excel?
The easiest and fastest way is through recording a macro. Go to the “Developer” and open a window for generating queries in VBA.
Didn’t find the “Developer”?
File -> Options -> Customize Ribbon
In the Main Tabs panel, check the Developer box and click OK.
Let’s start recording the macro. For convenience, you can give the macro a name.
We enter the command. In our case, in cell C6, we enter the formula
= C2 + C3 + C4 + C5
We stop recording the macro and check its work.
Change the numbers in cells C2 – C5. Go to “Macros” and click “Run”.
The result is calculated correctly.
What did the macro record? How does it look in code?
Go to “Developer” → “Macros” → select our macro → click “Change”
We see the code.
You can go to this field easier: just use the Alt + F11 combination, which takes us to the VBA code editor.
You can insert absolutely any macro into this field. If you are not an expert in VBA, it will be enough to spy on ready-made macros on the Internet and copy them into your document.
Examples of
1.Using macros, you can copy data from one file to another…
Sub CopyFiletoAnotherWorkbook ()
‘Copy the data
Sheets (“Example 1”) .Range (“A1: C5”) .Copy
‘Create a new workbook
Workbooks.Add
‘Paste the data
ActiveSheet.Paste
‘Turn off application alerts
Application.DisplayAlerts = False
‘Save the newly file. Change the name of the directory.
ActiveWorkbook.SaveAs Filename: =“C: EXAMPLE 2.xlsx”
‘Turn application alerts back on
Application.DisplayAlerts = True
End Sub
2. Using macros, you can delete takes. Earlier we wrote about how to do this without using VBA.
Sub DelDups_OneList ()
Dim iListCount As Integer
Dim iCtr As Integer
‘Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
‘Get count of records to search through.
iListCount = Sheets (“Sheet1”) .Range (“A1: A100”) .Rows.Count
Sheets (“Sheet1”) .Range (“A1”).Select
‘Loop until end of records.
Do until ActiveCell = “”
‘Loop through records.
For iCtr = 1 To iListCount
‘Don’t compare against yourself.
‘To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets (“Sheet1”) .Cells (iCtr, 1) .Row Then
‘Do comparison of next record.
If ActiveCell.Value = Sheets (“Sheet1”) .Cells (iCtr, 1) .Value Then
‘If match is true then delete row.
Sheets (“Sheet1”) .Cells (iCtr, 1) .Delete xlShiftUp
‘Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
‘Go to next record.
ActiveCell.Offset (1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox “Done!”
End Sub
The macro finds duplicate elements in the A1: A100 range and removes them. An important feature of the macro is the absence of empty cells in the array. If the list contains blank cells, you must sort the data in ascending order so that any blank cells appear at the end of the list.
3.Using macros, you can compare two lists and remove duplicate information…
The following example macro compares one (key) list with another and removes duplicate items in the second list that are in the main list. The first list is on Sheet1 in the range A1: A10. The second list is located on Sheet2 in the range A1: A100. To use the macro, select any sheet and then run the macro.
Sub DelDups_TwoLists ()
Dim iListCount As Integer
Dim iCtr As Integer
‘Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
‘Get count of records to search through (list that will be deleted).
iListCount = Sheets (“Sheet2”) .Range (“A1: A100”) .Rows.Count
‘Loop through the “master” list.
For Each x In Sheets (“Sheet1”) .Range (“A1: A10”)
‘Loop through all records in the second list.
For iCtr = 1 To iListCount
‘Do comparison of next record.
‘To specify a different column, change 1 to the column number.
If x.Value = Sheets (“Sheet2”) .Cells (iCtr, 1) .Value Then
‘If match is true then delete row.
Sheets (“Sheet2”) .Cells (iCtr, 1) .Delete xlShiftUp
‘Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox “Done!”
End Sub
The possibilities of VBA are quite extensive. We have given only a few examples, but you can study the topic in more detail on your own by enrolling in the Excel Academy course, watching training videos and our articles.
Alexander Valtsev, CEO of SF Education, spoke about one tricky macro for a giant table in the video.
Author: Fomkina Irina, expert at SF Education
