Editing an Excel MAcro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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()
 
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
 
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...
 
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
Excel 97 macros in cells? 2
macro to copy rows that contain a cell with a certain number 2
VBA Compare part of cell to another cell and then calculate 0
Loop to copy paste rows base on table arrays 0
Pivot date, that's all 6
exporting to Excel without overwriting the macros 4
Update this macro to make it generate a counter as text 7

Back
Top