|
Editing an Excel MAcro
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 |
Editing an Excel MAcro
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 |
Editing an Excel MAcro
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 |
Editing an Excel MAcro
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 |
| All times are GMT +1. The time now is 07:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com