Hi,


I have a range on a worksheet E7:M106. When certain conditions are met a row
in the range is moved to another worksheet.
Alternate rows are formatted with a different colour which needs to be
preserved. What would be the best way to move all the other rows up to
close this gap?

Thanks,

Albert

This should help! This procedure will delete all blank rows based on blank
cells that it detects in Col.A. Note: It is always good practice to setup a
copy of the workbook you are wanting to run this procedure to test, before
deleting live data.

Sub DeleteBlankRows()

' delete all blank rows, if no blank rows error occurs
On Error Resume Next
Sheets("Sheet1").Range("A3:A" & Cells(Rows.Count,
"A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
On Error GoTo 0

End Sub

Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan


"Albert Browne" wrote:


Hi,


I have a range on a worksheet E7:M106. When certain conditions are met a row
in the range is moved to another worksheet.
Alternate rows are formatted with a different colour which needs to be
preserved. What would be the best way to move all the other rows up to
close this gap?

Thanks,

Albert

Warning

See
http://www.rondebruin.nl/specialcells.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"RyanH" wrote in message ...
This should help! This procedure will delete all blank rows based on blank
cells that it detects in Col.A. Note: It is always good practice to setup a
copy of the workbook you are wanting to run this procedure to test, before
deleting live data.

Sub DeleteBlankRows()

' delete all blank rows, if no blank rows error occurs
On Error Resume Next
Sheets("Sheet1").Range("A3:A" & Cells(Rows.Count,
"A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
On Error GoTo 0

End Sub

Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan


"Albert Browne" wrote:


Hi,


I have a range on a worksheet E7:M106. When certain conditions are met a row
in the range is moved to another worksheet.
Alternate rows are formatted with a different colour which needs to be
preserved. What would be the best way to move all the other rows up to
close this gap?

Thanks,

Albert

interesting, that is good info to have! The data I use it for is only 400
rows long, but if something where to accidently happen and blow the Usedrange
up then I could be in trouble. I guess my users will have to wait a few
seconds longer to complete my macro.
--
Cheers,
Ryan


"Ron de Bruin" wrote:

Warning

See
http://www.rondebruin.nl/specialcells.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"RyanH" wrote in message ...
This should help! This procedure will delete all blank rows based on blank
cells that it detects in Col.A. Note: It is always good practice to setup a
copy of the workbook you are wanting to run this procedure to test, before
deleting live data.

Sub DeleteBlankRows()

' delete all blank rows, if no blank rows error occurs
On Error Resume Next
Sheets("Sheet1").Range("A3:A" & Cells(Rows.Count,
"A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
On Error GoTo 0

End Sub

Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan


"Albert Browne" wrote:


Hi,


I have a range on a worksheet E7:M106. When certain conditions are met a row
in the range is moved to another worksheet.
Alternate rows are formatted with a different colour which needs to be
preserved. What would be the best way to move all the other rows up to
close this gap?

Thanks,

Albert