Autofill Formula. Need help on the tweeking

[Davique]

Hi.

I have made a Macro that insert a new row below the Activerow, then its autofills from Cell E5 and down to the last row.
(the reason is that the E5 and down to lastrow has the same formula, and it autofills to make the formula change f.eks c5+d5 to be cc6+d6.)
This makro works, and it works well.

But i want the makro to insted Autofill from the activerow, and down to the new row below. Because then its easier to keep the background color. and font copy. (basicly want it to be a copy of the format in the line below, with the formula Autofilled.
(since the rows below the new inserted line auto-adjusts the formula)

But i cant seem to get the syntax correct.

'I use this to insert a new line:

Range("A" & (ActiveCell.Row)).Select
Selection.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'And this to Autofill from E5 and down to the last row in Coloum A:

lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("E5").Copy Range("E5:E" & lastrow)

 

[Joe4]

Welcome to the Board!

Does this do what you want?

'Capture row of active cell
myRow = ActiveCell.Row

'I use this to insert a new line:
Range("A" & myRow).Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'Copy formula from column E in ActiveCell row to new row
Range("E" & myRow).Copy Destination:=Range("E" & myRow + 1)

 

[Davique]

you know what? i did exactly what i wanted =) just perfection.
You sir, are today's big hero in my list.

I do however have one follow up question.

for my DeleteLine Macro i have the most simple code. ( i like it simple)

"
Sub deleteline()

' deleteline Macro

Selection.EntireRow.Delete
End Sub
"

Could you be so kind as to show me how to make it _not_ delete the row if there is _not_ a date(dd/mm/yyyy) in coloum A of the activerow?

 

[Joe4]

With this follow-up question, do you mean just one line, by itself, or do you mean to apply this logic to each row in your selection (assuming a multi-row selection)?

 

[Davique]

just one row. the active one. but i would like the delete function to not delete the line if its and date in colloum A.

 

[Joe4]

how me how to make it _not_ delete the row if there is _not_ a date(dd/mm/yyyy) in coloum A of the activerow?

i would like the delete function to not delete the line if its and date in colloum A.

I am a little confused if you want to delete the row if a date is in column A, or if it is not in column A. But it is just a minor tweak either way.
Dates are stored as numbers in Excel, specifically the number of days since 1/1/1900. So we just need to check the value in column A.

To delete the row if a date is in column A

If Cells(ActiveCell.Row,"A") > 0 Then
    ActiveCell.EntireRow.Delete
End If

To delete the row if in no date in column A

If Cells(ActiveCell.Row,"A") > 0 Then
Else
    ActiveCell.EntireRow.Delete
End If

 

[Davique]

yeah, i can see my english was pretty bad in that post.

let me try to clearify. If collum A contains a date-value, i want to makro to not deleterow.
- Note that the cells in A can contain normal text if there is not a date in there.

 

[Joe4]

let me try to clearify. If collum A contains a date-value, i want to makro to not deleterow.
- Note that the cells in A can contain normal text if there is not a date in there.

Did you try the last block of code from my previous task?