Moving some cells.
 

I am looking for code to do the following:

If cells J, K, L, & M are empty then move cells "E" to "D" and "H" t
"G". I need to do this on records 1-9999.

Thanks,

Me

--
Metraza
-----------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...fo&userid=3164
View this thread: http://www.excelforum.com/showthread.php?threadid=51839

Moving some cells.
 

Hi Metrazal,

Try this. Where I entered this formula in Column I and pulled down.

=COUNTA(J1:M1)

If column I is not usable then any free column will do, just chang the code
to reflect the other column instead of I1 and I100.

Sub IfEmptyMoveIt()
Dim i As Integer
Range("I1").Select
For i = 1 To Range("I100").End(xlUp).Row
If ActiveCell.Value = 0 Then
ActiveCell.Offset(, -4).Cut ActiveCell.Offset(, -5)
ActiveCell.Offset(, -1).Cut ActiveCell.Offset(, -2)
End If
ActiveCell.Offset(1, 0).Select
Next
End Sub

HTH
Regards,
Howard

"Metrazal" wrote in
message ...

I am looking for code to do the following:

If cells J, K, L, & M are empty then move cells "E" to "D" and "H" to
"G". I need to do this on records 1-9999.

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile:
http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518398

Moving some cells.
 

It seems to move the data regardless if data is in cells j:m and it
seems to stop at about 94 records. Any ideas?

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518398

Moving some cells.
 

Hi Met,

I somehow overlooked the 9999 row thing. So change

For i = 1 To Range("I100").End(xlUp).Row to:

For i = 1 To Range("I15000").End(xlUp).Row or to

For i = 1 To 9999

Did you insert the formula in column I and fill down to end of data?
And you say it moves stuff even if the cells are populated? Not sure whats
going on there.

Can you send me a sample workbook of what you have, don't need 9999 rows but
a true sample of about 30 - 50 rows of what you have.



Regards,
Howard

"Metrazal" wrote in
message ...

I am looking for code to do the following:

If cells J, K, L, & M are empty then move cells "E" to "D" and "H" to
"G". I need to do this on records 1-9999.

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile:
http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518398

Moving some cells.
 

Got it to work. I previously did not enter the calculation data in
column A. Thanks..

However, its very slow..

Does anyone know of a faster way to achieve this?

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518398

Moving some cells.
 

Hi Met,

I revised the code to a For each cell in Range/Next and it took about 11
seconds to do 11,000 rows. Is that faster than what than what you have?

Regards,
Howard

"Metrazal" wrote in
message ...

I am looking for code to do the following:

If cells J, K, L, & M are empty then move cells "E" to "D" and "H" to
"G". I need to do this on records 1-9999.

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile:
http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=518398