Macro that can cut paste rows, when values are blanks or only show two values in a row

For first problem try (UNTESTED)-

Please Login or Register  to view this content.

Check attached. Press blue button for results.

Sorry, I committed a small error, this would delete the rows also..

Please Login or Register  to view this content.

This is how the code works-

Please Login or Register  to view this content.

l1 is the last row on Sheet1

Please Login or Register  to view this content.

The macro runs for every value from k= 1 to k= l1 i.e. the last row number

Please Login or Register  to view this content.

Instead of going from all rows from 1 down to 956 we decide to go from 965 up to the first row.
This is because rows have to be deleted and this would create problems with the row numbers. Like if k=3 i.e. 3rd row is deleted then the 4th row becomes the 3rd row. But macro would go for k=4 next time i.e. row 4. This means we have missed a row in between that changed form row 4 to row 3 on deleting row 3.
So n is the row number calculated by subtracting k from l1 and adding 1
example-
for k=1, n=965-1+1=956
for k=2, n=956-2+1=955
and so on...

Please Login or Register  to view this content.

Checks if there are less than 3 values in columns H to CS

Please Login or Register  to view this content.

l2 is the last used row on sheet2 plus one i.e. the first unused row.

Please Login or Register  to view this content.

Puts the value from columns A to CS in Sheet2 if there are less than 3 values.

Please Login or Register  to view this content.

Deletes the row from Sheet1

Please Login or Register  to view this content.

If there are more than 2 values then the macro does nothing.

Please Login or Register  to view this content.

Goes for the next k and finally ends.

As I told macro runs from k=1 to k=965 (last row)
But the macro unfortunately would start from 1 then 2,3,4,5,6...... till 967

I will explain in detail both the situations. Lets us assume we had "k" instead of "n"-

Please Login or Register  to view this content.

(Application.CountA is to count number of cells that are not blank in the specified range)

So macro begins from k=1,
for k=1 we will have -

Please Login or Register  to view this content.

for k=2 we will have -

Please Login or Register  to view this content.

and so on...
So this is how this works.

But as I told the problem here is that if let us suppose row 2 of your spreadsheet has less than 3 values. So now it has to be deleted.

So for k=2 this will happen-
If Application.CountA(Sheets("Sheet1").Range("H2:CS2") < 3
(this would check if the number of values are less than 3 or not)
In this case we have assumed that there are less than two values.

So now if this is true macro does this-

Please Login or Register  to view this content.

assigns l2 the first unused row in Sheet2 starting from row 2.

Please Login or Register  to view this content.

Copies values of row 2 from sheet1 to first unused row of sheet2.

Please Login or Register  to view this content.

Deletes the row 2 from sheet1 after copying

Now as we have completed k=2, now macro moves to k=3. Here the problem occurs.
As we deleted row 2 row 3 must have become row 2 now.
But macro starts from k=3 i.e. row 3, so macro missed row 3 which became row 2 on deletion.
So instead of going from top to bottom we instead go from bottom to the top.
For this we calculate "n" instead of "k" by formula n = l1 - k + 1
example-
for k=1, n=965-1+1=965
for k=2, n=965-2+1=964
for k=3, n=965-3+1=963

So now assume that 964th row has less than 3 values and has to be deleted.
For k=2, n would be 964 i.e. 964th row will be deleted. and 965th row becomes 964th row.
So now we move to k=3
for k=3, n would be 963
So we see that we don't miss any row when we go from bottom to top as when rows are deleted all the rows are shifted up so even if we delete 964th row the macro would easily move to 963rd row.

This was the purpose behind using "n" instead of "k"