Help | AccountingWEB


HI I have a list of data with a 2 cell gap between each bit of data.  Is there a quick way to get rid of the blank celss and end up with a list of data running in rows 1,2,3,4,5 etc??



Fidodido | | Permalink

All sorted thanks

David Taylor's picture

Data with Gaps

David Taylor | | Permalink

The best bet is to use a Macro and here is one I use daily.  It allows you to choose the




* Start Row

* Increments

* Last Row

Sub DeleteRow()
    Application.ScreenUpdating = False
    Dim EndRow, CheckRows, I, StartRow, StepRow
    StartRow = Application.InputBox _
    ("Enter which row is the first to be removed." & Chr(10), _
    "Rows to delete - Start point", , , , , , 1)
    If TypeName(StartRow) = "Boolean" Then
        Exit Sub
    End If
    StepRow = Application.InputBox _
    ("Enter increment of n-th row to delete," & Chr(10) & _
    "i.e. 2 = every other, 3 every third?" & Chr(10), _
    "Rows to delete -  Step", , , , , , 1)
    If TypeName(StepRow) = "Boolean" Or StepRow <= 1 Then
         MsgBox "Sorry, do not remove every row with this code."
        Exit Sub
    End If
    EndRow = Application.InputBox _
    ("Enter which row is the last to be removed." & Chr(10), _
    "Rows to delete - End point", , , , , , 1)
    If TypeName(EndRow) = "Boolean" Then
        Exit Sub
    End If
    CheckRows = MsgBox("You want to remove rows in steps of " & StepRow _
    & ", starting with row " & StartRow & "  and ending with row " _
    & EndRow & ". Correct?", vbYesNo, "Verify data!")
    If CheckRows = vbYes Then
        I = StartRow
        Do Until I > EndRow
            Selection.Delete Shift:=xlUp
            I = I + StepRow - 1
    End If
    Application.Goto Reference:="R1C1"
    Application.ScreenUpdating = True
End Sub


Alternatively you could simply insert a column to the left of the data a autofill it with numbers 1 to X.

Then sort data by Column B thus putting all the blank rows together at either the top or bottom.

Delete the blank rows.

Sort Data by Column A

Delete Column A 

Two options there and they both work!!


Fidodido | | Permalink

Thanks David, i imagine you were typing whil i posted the repsonse. many thanks for taking the time.

I had a brainwave and basically did the second option to sort it.


David Taylor's picture

No problem

David Taylor | | Permalink

You are correct in saying that I was typing as you got the brainwave!!

Just pleased you found the solution but it is still worth putting the macro in your Personal.xls file for future reference as it is quicker.


Add comment
Log in or register to post comments