Help making macro finds last used line + push down and delete ! [Closed]

Report
Posts
1
Registration date
Thursday November 13, 2014
Status
Member
Last seen
November 13, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello guys,

im always here to find answer for my excel problems but couldnt find answer for this.

Every single day i have to do a job that is consuming my time and i would like to know if i can automize this.


But what I actualy want is that the macro finds the last row that is used (in this case, the last row that has data is 139) and push the selected row (139) to the next row (140). Im talking about date data, so it cant be copy-paste.

After that I need that the macro automatic ereases the cells I, J, E, and C that has been created at last (in this case on line 140). In other words, it must recognize what is the last row (that has just been created) and delete the cells specified

The macro is this one. (obviously with selection, since im selecting everything manually)

Range("B139:J139").Select
Selection.AutoFill Destination:=Range("B139:J140"), Type:=xlFillDefault
Range("B139:J140").Select
Range("I140").Select
Selection.ClearContents
Range("J140").Select
Selection.ClearContents
Range("E140").Select
Selection.ClearContents
Range("C140").Select
Selection.ClearContents

I tried using the lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row and other command many times and i cant get it work. i think im to dumm for this.

can someone give me a light? im willing to donate (paypal) if someone can help me getting this done, since is pissing me off. Sorry if is not that clear, english is not my main language.

Thanks everyone

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
you should have given a small extract of your data. see the saample data below in sheet1. as you are messing up with data copy sheet1 to shee2 before using the macro . the macro is given below. invoke only "test"



1 x x x x x x x x x
2 x x x x x x x x x
3 x x x x x x x x x
4 x x x x x x x x x
5 x c x e x x x i j



Sub test()
Dim lastrow As Long, r(1 To 4), j As Long
undo
Worksheets("sheet1").Activate
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastrow, 1).EntireRow.Insert shift:=xlDown
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set r(1) = Cells(lastrow, "I")
Set r(2) = Cells(lastrow, "J")
Set r(3) = Cells(lastrow, "E")
Set r(4) = Cells(lastrow, "C")
For j = 1 To 4
r(j).Clear
Next j
MsgBox "macro done"
End Sub


Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!