| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying an Old 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 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")="",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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying an Old Excel Macro
From Macro help
(http://support.microsoft.com/default...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 "LPS" wrote in message ... 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")="",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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying an Old Excel Macro
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 "Niek Otten" wrote: From Macro help (http://support.microsoft.com/default...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 "LPS" wrote in message ... 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")="",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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modifying an Old Excel Macro
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) "LPS" wrote in message ... 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")="",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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Modifying a protect unprotect macro | Excel Discussion (Misc queries) | |||
| Modifying Macro | Excel Discussion (Misc queries) | |||
| Need help modifying a macro | Excel Discussion (Misc queries) | |||
| Modifying Macro | Excel Worksheet Functions | |||
| Modifying Sheet1 macro to run on Sheet2 | Excel Discussion (Misc queries) | |||
Linear Mode
