Below we will look at a program in Excel VBA that deletes blank cells.
![Cells Cells](/uploads/1/2/5/2/125287366/290453440.png)
Situation:
1. First, we declare two variables of type Integer. One named counter and one named i. We initialize the variable counter with value 0.
2. Next, we check for each cell whether it is empty or not (<> means not equal to). We are using a loop for this. If not empty, we write the value to column B. The counter holds track of the number of cells that have been copied to column B. Each time we copy a value to column B, we increment counter by 1. This piece of the program looks as follows:
‘ Use.SpecialCells(xlCellTypeBlanks) to select the blanks,.Delete to delete, and ‘ the parameter Shift:= to determine how to shift the cells. Here, I use xlUp because ‘ that is how I need my data to flow. You can use xlLeft, xlDown, xlRight as necessary ‘ If you record this as a macro, you will get.Select, and.Selection.Delete.
For i = 1 To 10
If Cells(i, 1).Value <> ' Then
Cells(counter + 1, 2).Value = Cells(i, 1).Value
counter = counter + 1
EndIf
Next i
If Cells(i, 1).Value <> ' Then
Cells(counter + 1, 2).Value = Cells(i, 1).Value
counter = counter + 1
EndIf
Next i
Result so far:
3. Finally, we empty Range('A1:A10'), copy the values of column B to column A, and empty Range('B1:B10').
Range('A1:A10').Value = '
Range('A1:A10').Value = Range('B1:B10').Value
Range('B1:B10') = '
Range('A1:A10').Value = Range('B1:B10').Value
Range('B1:B10') = '
Result:
In this article, we will create a macro to delete incomplete records which contain blank cells.
Raw data consists of some sample data, which includes Name, Age and Gender. It also contains some blank cells.
We want to remove those records which contain blank cells.
Logic explanation
We have created “BlankRowDeletion” macro to delete incomplete records. It searches and selects blank cells and then deletes the entire row which contains a blank cell.
Code explanation
Set Rng = Range(“A9:C” & LastRow)
The above code is used to create a range object for the given data.
Rng.SpecialCells(xlCellTypeBlanks).Select
The above code is used to select blank cells within the specified range.
Selection.EntireRow.Delete
The above code is used to delete the entire row for the selected cell.
Please follow below for the code
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at [email protected]