Help | AccountingWEB

# Help

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??

### Sorted

All sorted thanks

### Data with Gaps

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
Rows(I).Select
Selection.Delete Shift:=xlUp
I = I + StepRow - 1
Loop
Else
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!!

### thanks

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.

Thanks