REARRANGEMENT OF CELL VALUES !!

[jay dean]

Hi -

I have data in Range("A:A. All the cells contain data contiguously
(i.e there is/are not empty cells in between).

However, some of the data in some cells are bound to be deleted. I need
a macro that will loop through the range and if there are any empty
cells, the macro will re-arrange the data (by moving them up) so that
empty cells will only be below the last entry.

Example: If the macro sees empty cells somewhere like:
A1:546
A2:908566
A3: <Empty Cell>
A4:57886
A5:43
A6:<Empty Cell>
A7:<Empty Cell>
A8:89432
...

Then the macro will re-arrange by moving the data up as:
A1:546
A2:908566
A3:57886
A4:43
A5:89432
A6:<Empty Cell>
A7:<Empty Cell>
A8:<Empty Cell>
...

SO IN THE END: All empty cells will be BELOW the last entry.I could
easily solve this problem by just looking for empty cells and deleting
the entirerow, but that is not an option, because I will lose data in
other columns of the sheet.

Any help will be appreciated.

Thanks
Jay Dean

 

[Bob Phillips]

Add a formula in B1 of

=LEN(A1)=0

copy it down as far as you need, then sort both columns, using B as the sort
key.

 

[jay dean]

Bob - Other parts of the worksheet contain lots and lots of data. I
can't insert another column (B) and sort as all cols are used.

I will need a macro to work solely only on the specified range and move
the data up.

Thanks for your suggestion though.

Jay

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

Application.ScreenUpdating = False

With ActiveSheet

.Columns("B").Insert
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("b1").Resize(LastRow).Formula = "=LEN(A1)=0"
.Rows("1:" & LastRow).Sort key1:=.Range("B1"), order1:=xlAscending,
header:=xlNo
.Columns("B").Delete
End With

Application.ScreenUpdating = True

End Sub

 

[jay dean]

Thanks, Bob --
If the data continued in, say, ranges ("A:A"), ("C:C"), ("E:E"), etc., I
can use your approach to sort each individual range. But if for example,
"A:A" has about a 1000 empty cells below its last entry and I need to
"pad" or fill those cells with the data in "C:C" until it is full and
continue in "C:C" from "E:E" until it is full, and so on, how will I
modify the code to execute this

This scenario has a very good possibility of occurring and I need to
prepare for that as well. I will appreciate any help.

Thanks again.
Jay