Modifying an Old Excel Macro

[LPS]

I have a macro (see below) which is designed (by someone else) to hide rows
that do not have any data in the current cell. Instead of hiding these rows,
I would like the macro to delete them.

I have tried to substitute the lines which change the row height to "0" with
the command: =edit.delete(3). This does not seem to work. Does anyone know
the correct syntax for a an older macro like this, to delete rows? Or, what
would all the code be if I converted the entire thing to Visual Basic. I am
a novice when it comes to this.

Any help will be greatly appreciated.

HideRows (H)
=ECHO(FALSE)
=ACTIVATE("Voucher")
=FORMULA.GOTO("Top_Item")
=ACTIVATE("Input")
=FORMULA.GOTO("Hide_items")
=SET.NAME("ItemNo",1)
=FOR("HIDE",1,2625)
= SELECT("RC")
= IF(OR(GET.FORMULA("RC")="0",GET.FORMULA("RC")="",DEREF(SELECTION())=0))
= ROW.HEIGHT(0)
= ACTIVATE("Voucher")
= ROW.HEIGHT(0)
= ELSE()
= ACTIVATE("Voucher")
= FORMULA(ItemNo)
= SET.NAME("ItemNo", ItemNo+1)
= END.IF()
= ACTIVATE("Voucher")
= SELECT("R[+1]C")
= ACTIVATE("Input")
= HIDE=HIDE+1
= SELECT("R[+1]C")
=NEXT()
=FORMULA.GOTO("R1C1")
=COLUMN.WIDTH(0)
=FORMULA.GOTO("R1C[+1]")
=ACTIVATE("voucher")
=FORMULA.GOTO("top_item")
=ECHO(TRUE)
=RETURN()

 

[Niek Otten]

From Macro help
(http://support.microsoft.com/default.aspx?scid=kb;en-us;128185):

Macro Sheets Only
Equivalent to choosing the Delete command from the Edit menu. Removes the
selected cells from the worksheet and shifts other cells to close up the
space.

Syntax

EDIT.DELETE(shift_num)
EDIT.DELETE?(shift_num)
Shift_num is a number from 1 to 4 specifying whether to shift cells left
or up after deleting the current selection or else to delete the entire row
or column.

Shift_num Result

1 Shifts cells left
2 Shifts cells up
3 Deletes entire row
4 Deletes entire column

If shift_num is omitted and if one cell or a horizontal range is selected,
EDIT.DELETE shifts cells up.
If shift_num is omitted and a vertical range is selected, EDIT.DELETE
shifts cells left.

Related Function

CLEAR Clears specified information from the selected cells or chart

 

[LPS]

Thank you Niek. I did try that code and it did not work. The required rows
were not deleted and adjacent cells had #REF errors in them, so I do not know
what I am doing wrong.

Suggestions????
--
LPS

From Macro help
(http://support.microsoft.com/default.aspx?scid=kb;en-us;128185):

Macro Sheets Only
Equivalent to choosing the Delete command from the Edit menu. Removes the
selected cells from the worksheet and shifts other cells to close up the
space.

Syntax

EDIT.DELETE(shift_num)
EDIT.DELETE?(shift_num)
Shift_num is a number from 1 to 4 specifying whether to shift cells left
or up after deleting the current selection or else to delete the entire row
or column.

Shift_num Result

1 Shifts cells left
2 Shifts cells up
3 Deletes entire row
4 Deletes entire column

If shift_num is omitted and if one cell or a horizontal range is selected,
EDIT.DELETE shifts cells up.
If shift_num is omitted and a vertical range is selected, EDIT.DELETE
shifts cells left.

Related Function

CLEAR Clears specified information from the selected cells or chart


--
Kind regards,

Niek Otten

I have a macro (see below) which is designed (by someone else) to hide rows
that do not have any data in the current cell. Instead of hiding these
rows,
I would like the macro to delete them.

I have tried to substitute the lines which change the row height to "0"
with
the command: =edit.delete(3). This does not seem to work. Does anyone
know
the correct syntax for a an older macro like this, to delete rows? Or,
what
would all the code be if I converted the entire thing to Visual Basic. I
am
a novice when it comes to this.

Any help will be greatly appreciated.

HideRows (H)
=ECHO(FALSE)
=ACTIVATE("Voucher")
=FORMULA.GOTO("Top_Item")
=ACTIVATE("Input")
=FORMULA.GOTO("Hide_items")
=SET.NAME("ItemNo",1)
=FOR("HIDE",1,2625)
= SELECT("RC")
=
IF(OR(GET.FORMULA("RC")="0",GET.FORMULA("RC")="",DEREF(SELECTION())=0))
= ROW.HEIGHT(0)
= ACTIVATE("Voucher")
= ROW.HEIGHT(0)
= ELSE()
= ACTIVATE("Voucher")
= FORMULA(ItemNo)
= SET.NAME("ItemNo", ItemNo+1)
= END.IF()
= ACTIVATE("Voucher")
= SELECT("R[+1]C")
= ACTIVATE("Input")
= HIDE=HIDE+1
= SELECT("R[+1]C")
=NEXT()
=FORMULA.GOTO("R1C1")
=COLUMN.WIDTH(0)
=FORMULA.GOTO("R1C[+1]")
=ACTIVATE("voucher")
=FORMULA.GOTO("top_item")
=ECHO(TRUE)
=RETURN()

 

[Bob Phillips]

Assuming it is checking column 1, here is some VBA

Sub HideRows()
Dim iLastRow As Long
Dim i As Long

For i = iLastRow To i Step -1
If Cells(i, "A").Value = "" Then
Rows(i).Delete
End If
Next i
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)