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 believe I have to substitute the
lines which change the row height to "0" with something else. Does anyone
know the correct syntax for a macro to delete rows? This is using Excel 2000.

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")="",D EREF(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()

--
LPS

Hi,

What version of Excel is that running in? It certainly doesn't look like
Visual Basic for Applications to me :)

I is think an old Excel 4 Macro that hasn't been upgraded to VBA.

The best solution is for you to replace that legacy code with a new VBA
procedure that runs in the Visual Basic Editor (Tools...Macro menu)

In MS Excel VBA, you would just use the Delete method on the EntireRow
properety of the range, or selection object ...

HTH

Philip

"LPS" wrote:

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 believe I have to substitute the
lines which change the row height to "0" with something else. Does anyone
know the correct syntax for a macro to delete rows? This is using Excel 2000.

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")="",D EREF(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()

--
LPS

Hi Philip. You are quite correct in saying that it is old code. It is being
used in Excel 2000 but it is not VBA and that is where I was having trouble.
If there was a simple solution (e.g.; just replace a few lines of code) then
that would be easier than re-creating the whole thing. But if I can't do
that, then recreate it I will.

Thx,

--
LPS


"Philip" wrote:

Hi,

What version of Excel is that running in? It certainly doesn't look like
Visual Basic for Applications to me :)

I is think an old Excel 4 Macro that hasn't been upgraded to VBA.

The best solution is for you to replace that legacy code with a new VBA
procedure that runs in the Visual Basic Editor (Tools...Macro menu)

In MS Excel VBA, you would just use the Delete method on the EntireRow
properety of the range, or selection object ...

HTH

Philip

"LPS" wrote:

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 believe I have to substitute the
lines which change the row height to "0" with something else. Does anyone
know the correct syntax for a macro to delete rows? This is using Excel 2000.

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")="",D EREF(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()

--
LPS

Any suggestions as to what the new code would be???
--
LPS


"Philip" wrote:

Hi,

What version of Excel is that running in? It certainly doesn't look like
Visual Basic for Applications to me :)

I is think an old Excel 4 Macro that hasn't been upgraded to VBA.

The best solution is for you to replace that legacy code with a new VBA
procedure that runs in the Visual Basic Editor (Tools...Macro menu)

In MS Excel VBA, you would just use the Delete method on the EntireRow
properety of the range, or selection object ...

HTH

Philip

"LPS" wrote:

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 believe I have to substitute the
lines which change the row height to "0" with something else. Does anyone
know the correct syntax for a macro to delete rows? This is using Excel 2000.

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")="",D EREF(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()

--
LPS