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??
All sorted thanks
The best bet is to use a Macro and here is one I use daily. It allows you to choose the
* Start Row
* 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 = TrueEnd 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 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.
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.
www.siftmedia.co.uk - Interactive community publishing
Registered in England & Wales No. 05923499
Terms & conditions |
Privacy and cookies