I am copying data, that is one long list, into an excel sheet and I want to use a macro to organize it visually. The problem is that the data I am copying has empty cells that are unnecessary and will inhibit my code later in the macro and I want to get rid of them. This is what I came up with, but I am hoping there is a more efficient way to do it. The main problem I ran into in trying other methods is I couldn't set a value to a variable to end the second "For" statement before it tried to select a cell that doesn't exist. After it got to the first row it would keep running and would return an error on "ActiveCell.Offset(-1, 0).Range("A1").Select" because there was no cell to select.
1, ,2, ,1, ,3, ,1, ,2, ,1, ,4, ,1, ,2, ,1, ,3, ,1, ,2, ,1,...
1, , ,1, , ,2, , ,1, , ,1, , ,2, , ,1, , ,1, , ,3, , ,1, ,...
Each comma represents a new cell, with the numbers and spaces (empty cells) in the same column.
Sub Organize_Data() Dim n As Integer Dim h As Integer Dim t As Integer h = 1000 t = h - 1 Cells(h, 1).Select For n = 1 To t If IsEmpty(ActiveCell) Then ActiveCell.Offset(-1, 0).Range("A1").Select h = h - 1 Else n = t End If Next n t = h - 1 For n = 1 To t If IsEmpty(ActiveCell) Then Selection.Delete Shift:=xlUp ActiveCell.Offset(-1, 0).Range("A1").Select h = h - 1 Else ActiveCell.Offset(-1, 0).Range("A1").Select h = h - 1 End If Next n End Sub
Data After Macro:
I was thinking that in place of the first "For" statement I could use
but I don't know how I would set the variable "t" so that the "For" Statement would end before it tried to select a cell that didn't exist. The other thing I can think of is maybe there is code that I could use for a third part to the "If" statement in the second "For" statement that will stop the "For" statement when it is in the first row.