Remove empty rows from a range
 

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

Remove empty rows from a range
 

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

Remove empty rows from a range
 

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

Remove empty rows from a range
 

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