VBA to delete lower cells on a range

[dualaudio454252]

I need a VBA code to delete empty cells under the filled cells since line 1, as usual.
I need this to a range of A:IV.

I have this:

1 1 1 1 1
2 2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
6 6 6
7 7 7
8 8
9
10

And I need this:

1
2
3 1
4 1 2
5 2 3
6 1 3 4
7 2 4 5
8 3 5 6
9 4 6 1 7
10 5 7 2 8

Actually there are dozens of columns and hundreds of lines, and I will do this not just one time.
I did a search for some code and tried to recording and change some actions, like:

Sub deljustthiscells()
Selection.Delete Shift:=xlDown
End Sub

Didn't work.

Or this, to make all the job from one time, which is what I really need:

Sub delcells()
Dim l As Long
l = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For l = l To 500 Step 1
If Sheets("Sheet1").Cells(l, 1) = "" Then
Cells(l, 1).Cell.Delete
End If
Next l
End Sub

Didn't work, too.

Guess it's something pretty simple from here.
This code should avoid hours of work.

If somebody can help me, please!

[:) Sixthsense :)]

Little bit confused and not sure how you are getting this...

3 1
4 1 2
5 2 3
6 1 3 4
7 2 4 5
8 3 5 6
9 4 6 1 7
10 5 7 2 8

[HaHoBe]

Hi, dualaudio454252,

any deletion on the lower cells will not have any effect on the upper cells to come down. I agree with SixtSense that you need to elaborate on how figures should not only be going down but across as well. In the meantime you may have a look at this macro which will insert empty clls on top and push the contents of each column down for as many cells as there are empty cells in the columns area based on Column A length of entries:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[dualaudio454252]

The attached file is the same, but the next text is the correct one.

[dualaudio454252]

Sixthsense:
Sorry. They shouldn'tbe. When I paste the numbers they get shuffled.
Please, see attached files, it shows the real situation.

HaHoBe:
Sorry too, it's xlDown.

Well, what I need is to drag the content of the columns with less lines until their last cell get aligned with the last cell of the column with more lines...
Plus, the lenght of all columns varies and the longest can be any of them.
Attachment 244076

[:) Sixthsense :)]

Thanks for the brief

Please make use of the code suggested by HaHoBe which will do the same task which you described

[dualaudio454252]

Sixthsense, I have tested the code of HaHoBe, but didn't mentioned cause it didn't work (that time).
I guess the point is "End(xlToLeft).Column".
It aligns all the lower cells by the leftest, yes?
Like I told you told you, the longest column can be any of them.
To test the code in a better situation, I paste the letter "A" in column A until line 500 and run the VBA.
IT DOES THE WORK!

So, to help me a little more, can you:
make the code recognize the longest column and align all the others?
or,
add a mark into the code so that it drags all the columns untill line 300, 500, etc, in such a way that I could change it manually?

It should fit my request at all!

[HaHoBe]

Hi, dualaudio454252,

a picture is worth a thousand words. But in an Excel Forum a Workbook is worth more than a thousand pictures as nobody is eager to construct what you have right before you: your workbook. Just try and paste the picture into a new workbook - can you run code on the picture? My Excel canīt.

Still not sure if you have pasted everything relevant but I assume that the range to work on always starts with A1:

Please Login or Register  to view this content.

Ciao,
Holger

[dualaudio454252]

Sorry, I know what's happening.
The best results I had so far were inconsistent, but that's cause there was some empty cells at the top of some columns.
So, the filled cells must begin in A1 and there can be no empty cells at the top of the columns, isn't it?
Solving this, the code works perfectly, even if A is not the longest column.

Thank you so much, HaHoBe!!!!!!!!!!!!!!!!!!!