Copy the Code:

Private Sub CommandButton1_Click()
'Through variant we can retrieve any data type
Dim i As Variant, R As Variant
' "i" for loop variable where as J for result column
R = 1 ' as result data starding from 1st cell
Dim last As Variant
' it results the last data in "A" column from "A10000"
last = Range("A10000").End(xlUp).Row
'Loop iterates from first to last cell
'from bottom of excel sheet to last data cell
'Cells(Rows.Count, "A").End(xlUp).Row

'We can use either of this

For i = 1 To last
'condition satisfies when first column value is not blank
If Trim(Cells(i, 1)) <> "" Then
'equalising second column value with first column
Cells(R, 2) = Cells(i, 1)
R = R + 1
End If
Next
End Sub

Remove the blank rows using special cells method

Copy the Code:

Private Sub CommandButton1_Click()
Dim lastrow As Integer
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To lastrow
If ThisWorkbook.Sheets("sheet2").Cells(i, 1) = "" Then
Rows(i).Delete
End If
Next
End Sub

Removing blank rows using COUNTA function

Copy the Code

Private Sub CommandButton1_Click()
Dim lastrow As Integer
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To lastrow
If WorksheetFunction.CountA(ThisWorkbook.Sheets("sheet2").Rows(i)) = 0 Then
Rows(i).Delete
End If
Next
End Sub

Remove Blank Rows

Private Sub CommandButton1_Click()
Dim lastrow As Integer, i As Integer
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = lastrow To 1 Step -1
If Application.CountA(Rows(i)) = 0 Then
Rows(i).Delete
End If
Next
End Sub

Remove a row based on Blank Cell

Private Sub CommandButton1_Click()
Dim lastrow As Integer
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = lastrow To 1 Step -1
If IsEmpty(Cells(i, 1)) Then
Rows(i).Delete
End If
Next
End Sub

Fill Blank Cells

Private Sub CommandButton1_Click()
Dim i As Integer
i = 1
Do Until Cells(i, 1) = 11
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1).Interior.ColorIndex = 5
MsgBox Cells(i, 1).Address
End If
i = i + 1
Loop
End Sub

Remove Blank cells - LEN Function

Private Sub CommandButton1_Click()
Dim last As Integer
last = Range("A" & Rows.Count).End(xlUp).Row
j = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To last
If Len(Cells(i, 1).Value) > 0 Then
Range("B" & j).Value = Cells(i, 1).Value
End If
j = Range("B" & Rows.Count).End(xlUp).Row + 1
Next
End Sub