VBA stands for visual basic for applications, and It's Microsoft's programming language for Excel. Excel has a lot of users around the world to analyze business financial performances. VBA is a pre-built program that automates tasks and performs several other functions beyond creating and organizing spreadsheets.
VBA is effective when it comes to more repetitive solutions. Like changing numbers transformed to dates, deleting columns, and rows, formatting multiple tables pasted on word, and many others. VBA macro code will be used to delete rows, and also it works on all versions of excel. (ms 2003, ms 2007, ms 2010, ms 2013)
1. Open Microsoft office excel or load up your worksheet. Select some rows.
2. Hold the alt key and press f11. It will open the VBA editor. On the mac, you can press function +
option + f11.
3. You will have the project explorer on the left when the editors open. When it is not there, go to the view tab on the excel ribbon. Click project explorer.
You can use the developer option to open the VBA editor. Is one of the methods used to open.
5. Click INSERT>MODULE. It will insert a module for the workbook when it opens; copy-paste this code.
Sub DeleteRowswithSpecificValue()
For i = Selection.Rows.Count To 1 Step -1
If Cells(i, 2).Value = "Printer" Then
Cells(i, 2).EntireRow.Delete
End If
Next i
End Sub
VBA script needs to be specific to get what you are expecting. For example, we want to delete blank rows.
You can delete using this code.
Sub DeleteBlankRows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Note the above code will delete the entire row if one cell is blank.
If we need to delete alternate rows, we can use the following code. In short, the code does not check if the row is blank. Use the formula with care. It will reduce data loss. You have to know what you want to delete. Careful you don't delete your datasets. The code will go through your workbook and delete every second row.
Sub DeleteAlternateRows()
RCount = Selection.Rows.Count
For i = RCount To 1 Step -2
Selection.Rows(i).EntireRow.Delete
Next i
End Sub
To delete rows with specific values or values. For example, delete rows with the name kings. Here is the code for this.
Sub DeleteRowswithSpecificValue()
For i = Selection.Rows.Count To 1 Step -1
If Cells(i, 2).Value = "Printer" Then
Cells(i, 2).EntireRow.Delete
End If
Next i
End Sub
VBA can also be used to delete columns and tables. Its universal can do every function in excel except the obvious. If you are not good at programming, you can use a VBA code generator. VBA generator is a program that will give you every code you are looking for. The code generator is OK, but I will advise you to practice and learn. In some cases code generator is 100%; it can distort your data. Frequent user of excel knows how sweet VBA can be if you know all the languages.