Editing an Excel MAcro

L

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

Philip

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
 
L

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 said:
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 said:
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")="",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()
Click to expand...
Click to expand...
 
L

LPS

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


Philip said:
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 said:
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")="",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()
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modifying an Old Excel Macro 3
convert old macros to vertual basic 0
Run macro on all files in a specific folder 3
Macro to delete row if cells greater than certain value 1
Configure macro to run on all sheets Excel 2k7 9
Update this macro to make it generate a counter as text 7
Macro autofills formula when a cell value is changed 4
Formula in macro gives "Run-time error '1004':" 1

Top