Deleting blank rows

[Guest]

I am trying to delete a series of rows from a spreadsheet using the following
code.

Public Sub DeleteRows()

Dim i As Integer


Do Until i = 500

With ActiveCell
If ActiveCell.Value = Nul Then
.EntireRow.Select
Selection.Delete
(Cells(1, 0)).Select

End If


End With
Selection(0).End(xlDown)(2).Select

Loop

End Sub

Once the row has been deleted I do not know how to move the selected cell
down three rows to the next range to be selected. Can anyone help

 

[Guest]

Alan
you could try
activecell.offset(2,0).select or something but it may be easier to work UP
the list
That way you do not need to skip rows

somthing like (not tested)

for i = 1000<usedrange.rows.count or something> to 1 step -1
if len(activesheet.cells(i,<col number>).formula)=0 then'its empty
activesheet.rows(i).delete
else
end if
next i

You'll need to modify this to suit what you are doing
cheers
Simon

 

[Guest]

Hi Alan,

I usually like to use something like:

CurrentRow = 1
Do
For ColumnNo = 1 to 10 ' the last column number
if (cells(CurrentRow, ColumnNo) <> "" Then
Exit For
end if
next
if (ColumnNo = 11) then ' the row is empty
Rows(CurrentRow).Delete
CurrentRow = CurrentRow - 1
end if
CurrentRow = CurrentRow + 1
Loop Until (CurrentRow = xxxx) ' xxxx = the last row

Ken

 

[Bob Flanagan]

Try something like this (untested)

For I = 500 to 1 step -1
if isempty(cells(I,1)) then rows(i).delete
Next

It is best to delete from the bottom up.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel