macro to delete the last value in each column

[Guest]

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

 

[Ron de Bruin]

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

 

[Don Guillett]

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

 

[Guest]

try

Sub marine()
Dim LastColumn As Long
With ActiveSheet
LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
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]

Why are you SELECTING? How do you know row 1 is the longest column?

lc = Cells.Find(what:="*", _
after:=Cells(1, 1), searchorder:=xlByColumns, _
searchdirection:=xlPrevious).Column

 

[Dave Thomas]

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

 

[Dave Thomas]

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 = ""

 

[Guest]

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

 

[Don Guillett]

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

 

[Dave Thomas]

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".

 

[Dave Thomas]

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]

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