Delete unused/part used rows?

[universal]

Hello All,

I have a macro which copy/pastes from a variety of workbooks into a ne
workbook. The nature of the data dictates that once it is collate
like this, the rows which have nothing in column A are redundant an
can be deleted.

This is what I have already (cobbled together from other peoples code)
it just doesnt seem to do what I'd thought:


Sub DeleteSpareRows()

Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Worksheets("AllWorkbooksCollated").Range("a5:a2500")

For Each MyCell In MyRange
If MyCell = "" Then
MyCell.Rows.Delete Shift:=xlUp
End If
Next

End Sub

For information, the rest of the row tends to have either data o
formulas, but is just not relevant.

Many thanks in advance,

E

 

[Roger Whitehead]

Quicker than Looping, if at least Column IV is free:

Sub QuickerThanLooping()
Columns(1).EntireColumn.Insert

With Range("B1", Range("B65536").End(xlUp))
.Offset(0, -1).FormulaR1C1 = _
"=IF(ISBLANK(RC[1]),1,"""")"
.Offset(0, -1) = .Offset(0, -1).Value
.Offset(0, -1).SpecialCells _
(xlCellTypeConstants, xlNumbers).EntireRow.Delete
.Offset(0, -1).EntireColumn.Delete
End With
End Sub

Inspired by Dave Hawley - www.ozgrid.com

 

[universal]

Thanks very much, that works a treat!

I hadnt thought of doing it that way.

If anyone knows why my original attempt didnt work though I would stil
be interested.......


Thanks
E

 

[Roger Whitehead]

If you use loops to delete rows, you must work upwards, otherwise as you
work down:

Current row is 20
Delete it
Row 21 is now 20
The For Each/Next loop moves cursor to row 20 + 1 (the old row 22). Row 21
has been missed altogether.

Better to get the lastrow
lastRow =
ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
or
lastrow = Range ("A65536").end(xlup).row 'Using the appropriate
column ref
then

For r = lastrow to 2 step -1 'Assuming labels in Row 1
'your code
next r