How to write this code

[Biznez]

Hi, im looking for a code that does this

In Sheet "Temp", find last value in column E and clear everything after from columns E to M

Thanks

 

[Biznez]

Module1 has the code

 

[Biznez]

first one was password protected...try this one instead

http://files.engineering.com/getfil...0-4a1b-9a73-04fe7303f003&file=SampleTest.xlsm

 

[SkipVought]

This is an example why words are so important.

You stated, "It has formula which i would like to clear at the end of the last value."

Which is what the code does!

What you should have stated was, "It has formula which i would like to clear at the end of the last [highlight #FCE94F]valid value that does not return an error[/highlight]"

So you got what you asked for!

[highlight #FCE94F]If you were to use column H instead of column E it would do AS I THINK YOU EXPECT. [/highlight] You ought to be able to figure that out.
You have lots of other problems in your sheet. You assume that all the dates in column H are the last day of the month. But that is an incorrect assumption, as the data proves.

And anytime you pre fill or over fill formulas, your logic is off, causing problems as you currently have.

You are asking the wrong questions.

 

[Biznez]

Im new at this....i tried H before and no luck

 

[SkipVought]

Post the code you tried.

 

[Biznez]

Dim x As Long
x = Sheets("Input").Range("A" & Rows.Count).End(3).Row + 5
Sheets("Temp").Range("E" & x & ":P" & Range("E" & Rows.Count).End(3).Row).Clear

 

[Biznez]

also tried this

Dim x As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
x = Sheets("Input").Range("A" & Rows.Count).End(3).Row + 6
Sheets("Temp").Range("E" & x & ":M" & Range("E" & Rows.Count).End(3).Row).Clear
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

 

[SkipVought]

I fail to see where you tried H WITH MY CODE!!!!!!

 

[Biznez]

im not too sure where to place your code
getting error message Run-Time error '91'

 

[Biznez]

i put your code in the vba module and still get this error

 

[SkipVought]

WHAT CODE? Please!

 

[Biznez]

Dim Rng As Range
With Sheets("Temp")
Set Rng = .Cells(.Rows.Count, "H").End(xlUp).Offset(1)
Intersect(.Range(Rng, .Cells(.Rows.Count, "H")).EntireRow, .UsedRange).EntireRow.Delete xlShiftUp
End With

 

[SkipVought]

so all the sudden when I run your Test code WITHOUT my code, it fills with some really crazy stuff to row 1000 while the INPUT sheet only has 139 rows?????

I'd use MS Query via Data > Get External Data > From other sources > From Microsoft query... and drill down to your workbook and query sheet INPUT. That will return ROWS FROM YOUR INPUT SHEET. Add Formulas as needed.

 

[Biznez]

INPUT sheet can have data upto 1000 rows. data is unpredictable from mainframe.

 

[SkipVought]

Only 1000 rows?

This is an unfounded assumption! Especially to pre-fill rows with formulas. BAD DESIGN!

You can query the input sheet data to return all or a portion of the data in whatever order you choose.