Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank rows
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 "Alan M" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank rows
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 "Alan M" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank rows
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 "Alan M" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Blank Rows | New Users to Excel | |||
Deleting blank rows | Excel Programming | |||
DELETING BLANK ROWS | Excel Programming | |||
deleting blank rows | Excel Programming | |||
Deleting Blank Rows | Excel Programming |