| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro to delete the last value in each column
My worksheets have dozens of columns, each with up to a few thousand rows.
Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
Hi z.entropic
You can find information here to find the last row number of a column http://www.rondebruin.nl/last.htm You can create a loop through all your columns and do the things you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
Be careful about running this twice.
Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
try
Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
Your macro is the reverse of his request. He wants to delete the last value
only if it's less than 90% of the penultimate value. Your macro is deleting the last value if the penultimate value is less than 90% of the last value. The code should be: If Cells(lr, i) < 0.9 * Cells(lr - 1, i) Then Cells(lr, i).Clear "Don Guillett" wrote in message ... Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
He wants to delete the last value only if it's less than 90% of the
penultimate value. Your macro is deleting the last value if it's greater than 90% of the penultimate value. The code should be: If ActiveCell.Value < (ActiveCell.Offset(-1, 0).Value * 0.9) Then ActiveCell.Value = "" "Mike H" wrote in message ... try Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
Yes, Dave, that's my intent, to delete the last value only and only if it is
LESS than 90% of the penultimate value, including "0", which crashes the calcs. z.entropic "Dave Thomas" wrote: He wants to delete the last value only if it's less than 90% of the penultimate value. Your macro is deleting the last value if it's greater than 90% of the penultimate value. The code should be: If ActiveCell.Value < (ActiveCell.Offset(-1, 0).Value * 0.9) Then ActiveCell.Value = "" "Mike H" wrote in message ... try Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
Use this
Sub deletelastiflessthan90() lc = Cells.Find(what:="*", _ after:=Cells(1, 1), searchorder:=xlByColumns, _ searchdirection:=xlPrevious).Column For i = 1 To lc lr = Cells(Rows.Count, i).End(xlUp).Row 'MsgBox lr If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... Yes, Dave, that's my intent, to delete the last value only and only if it is LESS than 90% of the penultimate value, including "0", which crashes the calcs. z.entropic "Dave Thomas" wrote: He wants to delete the last value only if it's less than 90% of the penultimate value. Your macro is deleting the last value if it's greater than 90% of the penultimate value. The code should be: If ActiveCell.Value < (ActiveCell.Offset(-1, 0).Value * 0.9) Then ActiveCell.Value = "" "Mike H" wrote in message ... try Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column
Don, am I not understanding the code? Is the code saying to compare the
value before the last value found in the column and if the value before the last value found is less than 90% of the last value found, delete the last value found? If the code is saying that, the code is wrong. He wants to delete the last value found only if it less than the penultimate value which is the value before the last value found. For example: if the last value found is in C101 the penultimate value is therefore in C100. If C101 is less than 90% of C100 then delete the value in C101. Your code is saying, if I understand it, that if C100 is less than 90% of C101, delete the value in C101. I think we have a failure to communicate here because of the word penultimate which means "next to last". "Don Guillett" wrote in message ... Use this Sub deletelastiflessthan90() lc = Cells.Find(what:="*", _ after:=Cells(1, 1), searchorder:=xlByColumns, _ searchdirection:=xlPrevious).Column For i = 1 To lc lr = Cells(Rows.Count, i).End(xlUp).Row 'MsgBox lr If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... Yes, Dave, that's my intent, to delete the last value only and only if it is LESS than 90% of the penultimate value, including "0", which crashes the calcs. z.entropic "Dave Thomas" wrote: He wants to delete the last value only if it's less than 90% of the penultimate value. Your macro is deleting the last value if it's greater than 90% of the penultimate value. The code should be: If ActiveCell.Value < (ActiveCell.Offset(-1, 0).Value * 0.9) Then ActiveCell.Value = "" "Mike H" wrote in message ... try Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column - 90% correction
"Dave Thomas" wrote in message . net... Don, am I not understanding the code? Is the code saying to compare the value before the last value found in the column and if the value before the last value found is less than 90% of the last value found, delete the last value found? If the code is saying that, the code is wrong. He wants to delete the last value found only if it less than 90% of the penultimate value which is the value before the last value found. For example: if the last value found is in C101 the penultimate value is therefore in C100. If C101 is less than 90% of C100 then delete the value in C101. Your code is saying, if I understand it, that if C100 is less than 90% of C101, delete the value in C101. I think we have a failure to communicate here because of the word penultimate which means "next to last". "Don Guillett" wrote in message ... Use this Sub deletelastiflessthan90() lc = Cells.Find(what:="*", _ after:=Cells(1, 1), searchorder:=xlByColumns, _ searchdirection:=xlPrevious).Column For i = 1 To lc lr = Cells(Rows.Count, i).End(xlUp).Row 'MsgBox lr If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... Yes, Dave, that's my intent, to delete the last value only and only if it is LESS than 90% of the penultimate value, including "0", which crashes the calcs. z.entropic "Dave Thomas" wrote: He wants to delete the last value only if it's less than 90% of the penultimate value. Your macro is deleting the last value if it's greater than 90% of the penultimate value. The code should be: If ActiveCell.Value < (ActiveCell.Offset(-1, 0).Value * 0.9) Then ActiveCell.Value = "" "Mike H" wrote in message ... try Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
macro to delete the last value in each column - 90% correction
A re-read confirms that you are correct. So, minor correction to
If Cells(lr, i) < 0.9 * Cells(lr - 1, i) Then Cells(lr, i).Clear -- Don Guillett Microsoft MVP Excel SalesAid Software "Dave Thomas" wrote in message . net... "Dave Thomas" wrote in message . net... Don, am I not understanding the code? Is the code saying to compare the value before the last value found in the column and if the value before the last value found is less than 90% of the last value found, delete the last value found? If the code is saying that, the code is wrong. He wants to delete the last value found only if it less than 90% of the penultimate value which is the value before the last value found. For example: if the last value found is in C101 the penultimate value is therefore in C100. If C101 is less than 90% of C100 then delete the value in C101. Your code is saying, if I understand it, that if C100 is less than 90% of C101, delete the value in C101. I think we have a failure to communicate here because of the word penultimate which means "next to last". "Don Guillett" wrote in message ... Use this Sub deletelastiflessthan90() lc = Cells.Find(what:="*", _ after:=Cells(1, 1), searchorder:=xlByColumns, _ searchdirection:=xlPrevious).Column For i = 1 To lc lr = Cells(Rows.Count, i).End(xlUp).Row 'MsgBox lr If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... Yes, Dave, that's my intent, to delete the last value only and only if it is LESS than 90% of the penultimate value, including "0", which crashes the calcs. z.entropic "Dave Thomas" wrote: He wants to delete the last value only if it's less than 90% of the penultimate value. Your macro is deleting the last value if it's greater than 90% of the penultimate value. The code should be: If ActiveCell.Value < (ActiveCell.Offset(-1, 0).Value * 0.9) Then ActiveCell.Value = "" "Mike H" wrote in message ... try Sub marine() Dim LastColumn As Long With ActiveSheet LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn End With For x = 1 To LastColumn Columns(x).Select ActiveCell.Offset(65535, 0).End(xlUp).Select If ActiveCell.Value (ActiveCell.Offset(-1, 0).Value * 0.9) Then _ ActiveCell.Value = "" Next End Sub Right ckick sheet tab view code and paste in Mike "Don Guillett" wrote: Be careful about running this twice. Sub deletelastiflessthan90() For i = 1 To 36' chg to suit lr = Cells(Rows.Count, i).End(xlUp).Row If Cells(lr - 1, i) < 0.9 * Cells(lr, i) Then Cells(lr, i).Clear Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "z.entropic" wrote in message ... My worksheets have dozens of columns, each with up to a few thousand rows. Both enumber of columns and rows varies from one data set tp another. I'd like to have a macro that would delete the last value in each column, but only if it's less than 90% of the penultimate value in that column. Obviously, simply recording a macro with a Ctrl-Down key sequence in each column will not work as the the last value is recorded as a static address, but maybe using the OFFSET function would... Help, please... z.entropic |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete a Column when in a Macro-Worksheet Event? | Excel Worksheet Functions | |||
| delete a macro that isn't in macro list | Excel Discussion (Misc queries) | |||
| How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
| set up a macro to delete characters in each cell of a column | Excel Discussion (Misc queries) | |||
| How do i delete a macro in Excel 2003 when delete isn't highlight | Excel Discussion (Misc queries) | |||
Linear Mode
