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

 

[SkipVought]

We already talked about this condition.

You have this because your Screen Navigation is lacking precision.

Fix your screen navigation, which I ranked as of PRIMARY imporance before any other coding occurs, and you won't need to crutch your workbook!

 

[Biznez]

Hey Skip, i already fixed that issue. This is an excel problem im having. It has formula which i would like to clear at the end of the last value

 

[SkipVought]

Why do you have FORMULAS in unused rows?

Excel has the Structured Table feature where as you add rows of data to the table, all formulas are propogated to tha added rows.

It is a bad practice to PRE-FILL formulas on a table.

Insert > Tables > Table

 

[Biznez]

if you dont mind, if you know the code to what i want it to do, can you please post it

 

[SkipVought]

Just delete the data, make your table a Strutured Table via Insert > Tables > Table, then any new row in the table will contain the formulas you have in prior rows.

 

[SkipVought]

Please clarify what you want this code to do, as what you're asking for is ambiguous.

 

[Biznez]

basically In Sheet "Temp", find last value in column E and clear everything after from columns E to M. These rows have formulas in them and i dont want them there

 

[SkipVought]

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

 

[Biznez]

getting error message Run-Time error '91'
Object variable or with block variable not set

 

[SkipVought]

I tested this and it works.

Where is your code?

 

[SkipVought]

That is where is your code located?

 

[Biznez]

i put it at the end of everything before end sub

 

[SkipVought]

It must be in an Excel VBA Module AFTER the last End Sub if there are any procedures in the module.

 

[Biznez]

there are no procedures in the module

 

[Biznez]

this is the code im using

sub Test()
Worksheets("Temp").Range("E10:E1000").Formula = "=H10+1"
Worksheets("Temp").Range("F11:F1000").Formula = "=H10+1"
Worksheets("Temp").Range("F10").Formula = "=StartDate"
'Worksheets("Temp").Range("J10:J100").Formula = "=MIN(I10,P10+M9)"
'Worksheets("Temp").Range("K10:K100").Formula = "=MAX(I10-J10,0)"
'Worksheets("Temp").Range("L10:L100").Formula = "=L9-K10"
Worksheets("Temp").Range("J10:J1000").Formula = "=Input!D5"
Worksheets("Temp").Range("K10:K1000").Formula = "=Input!E5"
Worksheets("Temp").Range("L10:L1000").Formula = "=Input!F5"
Worksheets("Temp").Range("M10:M1000").Formula = "=MAX(P10-J10+M9,0)"
Worksheets("Temp").Range("P10").Formula = "=ROUND(PPint(F10,H10-1,Rate,L9)+O10,2)"
Worksheets("Temp").Range("P11:P1000").Formula = "=ROUND(PPint(F11-1,H11-1,Rate,L10)+O11,2)"
Worksheets("Temp").Range("L9").Formula = "=StartBal"
'Worksheets("Temp").Range("B9").Formula = "=Input!A5"
Worksheets("Temp").Range("B18").Formula = "=B17-B16"
Worksheets("Temp").Range("B19").Formula = "=SUM(O10:O132)"
Worksheets("Temp").Range("I2").Formula = "=Input!D2"
Worksheets("Temp").Range("I3").Formula = "=Input!C2"

Worksheets("Temp").Range("G10:G1000").Formula = "=Input!B5"
Worksheets("Temp").Range("H10:H1000").Formula = "=Input!A5"
Worksheets("Temp").Range("I10:I1000").Formula = "=Input!C5"
Sheets("Input").Range("C5:F1000").Replace "-", ""
'Sheets("Input").Range("C2").Replace "-", ""
'Sheets("Temp").Range("I10:K1000").Replace "-", ""

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

 

[SkipVought]

that's because you have NO ROW where the last row with a column E value is less than the last row of the values in columns E:M!!!

This sheet is a MESS!

 

[Biznez]

how to fix this?

 

[SkipVought]

Need to see your workbook. Please upload to Engineering.com as instructed below the [highlight blue]Reply To This Thread window[/highlight].

 

[Biznez]

http://files.engineering.com/getfil...8-4711-b640-1ed5751710b5&file=SampleTest.xlsm